文章目錄
SQL SERVER BI 入門
或許有很多同學和我一樣,只是聽說過BI這個詞彙,但如果工作不涉及到資料統計分析或資料採礦,很難接觸這方面的知識。我以前一直嚮往能在這方面有所曆練,恰巧最近公司需要做資料統計和分析的工作,這份工作我主動提出來做,榮幸之至。寫這篇文章也是對我最近工作的總結。
下載並安裝
我工作選用的SqlServer 2008 R2,由於在自己電腦上寫部落格,所以我這裡嘗試使用2012版本,順便也能看看哪裡不同。 是http://www.microsoft.com/en-us/download/details.aspx?id=29066如果你的系統是中文的則選擇中文版。
安裝時選擇功能模組如下:[配圖1]
請注意右下角的Prerequisites for selected features的提示內容,需要4.0,其實還需要3.5。
基本概念介紹
資料分析主要包含如下內容:
未經處理資料庫
未經處理資料庫主要是用於一些資料上報的資料存放區,它包含的是最原始的資訊,比如一個使用者在什麼時間訪問了什麼頁面或點擊了哪些按鈕。這些資料可以通過js、as、或後端代碼進行上報。
這種日誌性的資料上報一般量非常大,一天可能就產生幾億條資料,之前我在一家廣告公司呆過,廣告的量大的驚人,因為廣告都是在各大門戶展現的,所以頁面的PV就是廣告的條數,使用者的相關操作還會有資料產生,每天十來G的資料量都不成問題,所以未經處理資料庫表的設計要注意幾點:
1、不能有索引(除了主鍵),也不需要索引,因為相關的分析統計都在資料倉儲裡進行。
2、主鍵必須是有序的主鍵,如果是GUID之類的,就無法保證順序,在這資料插入時會調整資料存放區的物理順序,這是非常恐怖的事情,影響速度。
3、如果資料非常大,要考慮採用分區或分庫儲存。
4、如果瞬間插入的太多資料庫壓力大,則需要考慮增加一個緩衝層來緩解壓力,這便需要編寫服務對緩衝層資料進行整理插入到資料庫的工作。這樣也的缺點是萬一快取服務掛掉,有可能會有資料丟失的情況,可以選用會持久化的快取服務。總之,這些是需要權衡的。
資料倉儲資料庫
資料倉儲資料庫,是必須的,所有的統計分析都需要以此為基礎。資料倉儲的表分為兩種:維度資料表(dimension)和事實表(fact)。
1. 維度資料表
維度很容易理解。例如,我們想知道每天有多少使用者使用了產品,那麼“每天”就是一個維度,因為我們需要安裝“天”來查詢有多少使用者。同樣,年、月、周、季度、地區等都是我們最常見的維度。
2. 事實表
事實表的理解可能會模糊一些。一般可以理解為我們要對哪種資料做統計,這種資料事實產生了哪些記錄。比如每個使用者每一個操作,這是一個事實。那麼我們對使用者的操作行為做統計的時就需要行為的事實表。
3. 事實表和維度資料表的關係
如果我們使用時間維度對使用者行為做統計,那麼事實表必須要有一個時間欄位。而時間欄位的儲存其實是時間維度表的主鍵ID,而不是真正的時間,[配圖2]
注意,我的事實表FactUserAction(使用者操作行為事實)的OperateDate是int類型,和維度資料表DimDate建立了關係,而且DimDate把日期拆成了年月日三個欄位,因為統計可能需要對年和月進行統計,所以這麼設計;他們之間有一個層次關係,我們後面會講。原始表的設計
細心的朋友可能會發現,事實表的UserAction也是一個int,是的,其實這也是一個查詢維度,只是我們暫時只拿時間做例子。
4. 如何設計事實表和維度資料表
事實表和維度設計主要是營運和產品人員的需求而決定的。程式員必能因為開發的複雜而拒絕他們的需求。當然,也不能無理的需求。這篇文章就以時間維度的需求做講解吧,這個一般也是必須的需求。
5. 事實表和維度資料表的資料填充
這一項對我們程式員來講是最為簡單的,因為我們可以開發一個服務,定時從未經處理資料庫把資料按照倉庫的設計重組,並插入。SQLSERVER BI也提供了現有的工具,這種工具稱之為ETL(Extraction-Transformation-Loading資料擷取、轉換和下載),在SQLSERVERBI裡叫SSIS(SqlServer Integration Service)。
圖片裡三個方塊是從一個表裡讀出資料,然後通過一個列的轉換,最後把轉換後的列映射到目標庫的表裡,我這裡做的是把CreateTime欄位轉換成Year Month 和 Day,然後插入到DimDate表
使用ETL要比自己寫匯入程式更能方便的和Analysis Services互動。比如匯入程式後執行唯獨表,比如各種資料來源的整合,非常方便。不過我沒有做深入研究。暫時就不寫這一塊了,有興趣的朋友可以慢慢摸索下。
今天就先寫這麼多,因為我為了,安裝SQLServer費了好大勁,.NET Framework 3.5總是安裝時下載失敗。我的系統是windows8,需要單獨安裝3.5才行。
大家先瞭解這些概念,不太明白的概念,可以先百科一下。下節寫關於Analysis services的使用。