SQL SERVER BI 入門

或許有很多同學(xué)和我一樣，只是聽說過BI這個詞匯，但如果工作不涉及到數(shù)據(jù)統(tǒng)計分析或數(shù)據(jù)挖掘，很難接觸這方面的知識。我以前一直向往能在這方面有所歷練，恰巧最近公司需要做數(shù)據(jù)統(tǒng)計和分析的工作，這份工作我主動提出來做，榮幸之至。寫這篇文章也是對我最近工作的總結(jié)。

下載并安裝

我工作選用的SqlServer 2008 R2，由于在自己電腦上寫博客，所以我這里嘗試使用2012版本，順便也能看看哪里不同。 下載地址是http://www.microsoft.com/en-us/download/details.aspx?id=29066如果你的系統(tǒng)是中文的則選擇中文版。

安裝時選擇功能模塊如下：[配圖1]

請注意右下角的Prerequisites for selected features的提示內(nèi)容，需要4.0，其實(shí)還需要3.5。

基本概念介紹

數(shù)據(jù)分析主要包含如下內(nèi)容：

原始數(shù)據(jù)庫

　　原始數(shù)據(jù)庫主要是用于一些數(shù)據(jù)上報的數(shù)據(jù)存儲，它包含的是最原始的信息，比如一個用戶在什么時間訪問了什么頁面或點(diǎn)擊了哪些按鈕。這些數(shù)據(jù)可以通過js、as、或后端代碼進(jìn)行上報。

　　這種日志性的數(shù)據(jù)上報一般量非常大，一天可能就產(chǎn)生幾億條數(shù)據(jù)，之前我在一家廣告公司呆過，廣告的量大的驚人，因?yàn)閺V告都是在各大門戶展現(xiàn)的，所以頁面的PV就是廣告的條數(shù)，用戶的相關(guān)操作還會有數(shù)據(jù)產(chǎn)生，每天十來G的數(shù)據(jù)量都不成問題，所以原始數(shù)據(jù)庫表的設(shè)計要注意幾點(diǎn)：

　　　　1、不能有索引（除了主鍵），也不需要索引，因?yàn)橄嚓P(guān)的分析統(tǒng)計都在數(shù)據(jù)倉庫里進(jìn)行。

　　　　2、主鍵必須是有序的主鍵，如果是GUID之類的，就無法保證順序，在這數(shù)據(jù)插入時會調(diào)整數(shù)據(jù)存儲的物理順序，這是非?？植赖氖虑?，影響速度。

　　　　3、如果數(shù)據(jù)非常大，要考慮采用分區(qū)或分庫存儲。

　　　　4、如果瞬間插入的太多數(shù)據(jù)庫壓力大，則需要考慮增加一個緩存層來緩解壓力，這便需要編寫服務(wù)對緩存層數(shù)據(jù)進(jìn)行整理插入到數(shù)據(jù)庫的工作。這樣也的缺點(diǎn)是萬一緩存服務(wù)掛掉，有可能會有數(shù)據(jù)丟失的情況，可以選用會持久化的緩存服務(wù)?？傊@些是需要權(quán)衡的。

數(shù)據(jù)倉庫數(shù)據(jù)庫
　　數(shù)據(jù)倉庫數(shù)據(jù)庫，是必須的，所有的統(tǒng)計分析都需要以此為基礎(chǔ)。數(shù)據(jù)倉庫的表分為兩種：維度表（dimension）和事實(shí)表（fact）。

　　1. 維度表
　　維度很容易理解。例如，我們想知道每天有多少用戶使用了產(chǎn)品，那么“每天”就是一個維度，因?yàn)槲覀冃枰惭b“天”來查詢有多少用戶。同樣，年、月、周、季度、地區(qū)等都是我們最常見的維度。

　　2. 事實(shí)表
　　事實(shí)表的理解可能會模糊一些。一般可以理解為我們要對哪種數(shù)據(jù)做統(tǒng)計，這種數(shù)據(jù)事實(shí)產(chǎn)生了哪些記錄。比如每個用戶每一個操作，這是一個事實(shí)。那么我們對用戶的操作行為做統(tǒng)計的時就需要行為的事實(shí)表。

　　3. 事實(shí)表和維度表的關(guān)系
　　如果我們使用時間維度對用戶行為做統(tǒng)計，那么事實(shí)表必須要有一個時間字段。而時間字段的存儲其實(shí)是時間維度表的主鍵ID，而不是真正的時間，如圖：[配圖2]

注意，我的事實(shí)表FactUserAction（用戶操作行為事實(shí)）的OperateDate是int類型，和維度表DimDate建立了關(guān)系，而且DimDate把日期拆成了年月日三個字段，因?yàn)榻y(tǒng)計可能需要對年和月進(jìn)行統(tǒng)計，所以這么設(shè)計；他們之間有一個層次關(guān)系，我們后面會講。原始表的設(shè)計如圖

　　細(xì)心的朋友可能會發(fā)現(xiàn)，事實(shí)表的UserAction也是一個int，是的，其實(shí)這也是一個查詢維度，只是我們暫時只拿時間做例子。

　　4. 如何設(shè)計事實(shí)表和維度表
　　事實(shí)表和維度的設(shè)計主要是運(yùn)維和產(chǎn)品人員的需求而決定的。程序員必能因?yàn)殚_發(fā)的復(fù)雜而拒絕他們的需求。當(dāng)然，也不能無理的需求。這篇文章就以時間維度的需求做講解吧，這個一般也是必須的需求。

　　5. 事實(shí)表和維度表的數(shù)據(jù)填充
　　這一項對我們程序員來講是最為簡單的，因?yàn)槲覀兛梢蚤_發(fā)一個服務(wù)，定時從原始數(shù)據(jù)庫把數(shù)據(jù)按照倉庫的設(shè)計重組，并插入。SQLSERVER BI也提供了現(xiàn)有的工具，這種工具稱之為ETL（Extraction-Transformation-Loading數(shù)據(jù)提取、轉(zhuǎn)換和加載），在SQLSERVERBI里叫SSIS（SqlServer Integration Service）。

　　圖片里三個方塊是從一個表里讀出數(shù)據(jù)，然后通過一個列的轉(zhuǎn)換，最后把轉(zhuǎn)換后的列映射到目標(biāo)庫的表里，我這里做的是把CreateTime字段轉(zhuǎn)換成Year Month 和 Day，然后插入到DimDate表

　　使用ETL要比自己寫導(dǎo)入程序更能方便的和Analysis Services交互。比如導(dǎo)入程序后執(zhí)行唯獨(dú)表，比如各種數(shù)據(jù)源的集成，非常方便。不過我沒有做深入研究。暫時就不寫這一塊了，有興趣的朋友可以慢慢摸索下。

今天就先寫這么多，因?yàn)槲覟榱私貓D，安裝SQLServer費(fèi)了好大勁，.NET Framework 3.5總是安裝時下載失敗。我的系統(tǒng)是windows8，需要單獨(dú)安裝3.5才行。

大家先了解這些概念，不太明白的概念，可以先百科一下。下節(jié)寫關(guān)于Analysis services的使用。