第二天大部分的時間都在實機操作，能筆記下來的以小技巧或者觀念為主，也難免雜亂了些： 

1. SQL 2012中以SSDT(SQL Server Data Tools)做為資料分析專案的開發工具，過往SQL 2008 BIDS已經消失不見。
   • 可開發/測試SQL Servr相關的專案。
   • 可建立安裝佈署檔。
   • 可開發BISM模型專案，包含Multi-Dimension以及Tabular兩種類型。
2.  Excel Power Pivot：
   • 在本機記憶體內做資料運算。
   • 從Excel 啟動 Power Pivot，在直接匯入維度與事實資料表後，第一步便是建立欄位之間的關聯性。
   • 如果需要，再進行欄位名稱的修改。
   • Fact Table不要置放有除法結果的欄位；如毛利率。
   • 切換到Excel樞扭分析表後，一個樞紐分析圖表，必定只對應一個工作底稿。
   • 除了可在本機的Excel檔案內運算外，也可發佈到Sharepoint上提供大家使用
     • 發佈到Sharepoint後的檔案為單純的HTML檔格式，支援跨瀏覽器觀看。
     • 在發佈後，便會實際的建立Cube結構體。
     • 發佈方式為【檔案】\【儲存並傳送】\儲存至Sharepoint，選擇對應的Sharepoint Server可佈署的文件庫，勾選【儲存縮圖】即可。
     • 若在Sharepoint上以Excel模式打開編輯，則會以Server-Side 的Memory當作計算的資源。
     • 可排程更新Cube資料。
3. Power View：
   • Image在DB中一律存成VARBINARY(MAX)
   • 不支援父子式階層
   • 類似投影片的編輯模式，透過物件的拖拉方式完成可高度互度的報表服務。
   • 只能在區網內觀看。
4. DAX：
   • 屬於中繼語言，使用並擴充了Excel內建的函式庫；最終會被轉譯成MDX後再執行。
   • C#為其編譯器，故區分大小寫。
   • 需特別注意，空值有DBNull以及BLANK()兩種類型；判斷上皆用BLANK()以確認是否真的沒有值在Cell中。
   • 內建函式中，有許多簡化過的擴充函式，對財務運算特別有用，如：
     • TotalMTD(月累計)
     • TotalQTD (季累計)
     • TotalYTD (年累計)
     • PreviousDay(去年同日比較)
     • PreviousMonth (去年同月比較)
     • PreviousQuarter (去年同季比較)
     • PreviousYear (去年同年比較)
5. Data Explore：
   • 屬自助式服務的ETL工具
   • 於記憶體內運算
   • Metro Type
   • 可將各式各樣的大廠資料來源做成Data Feed (RSS，底層為Web Service；而2008 R2後SSRS便可將Data Source包成RSS)供異質資料的整合。

今明兩天都在上尹相志老師的SQL Server 2012 商業智慧實務的研習專班，今日第一天有些小筆記，寫下來日後供參： 

1. SQL 2012加入了BISM (BI Semantic Model)的新商業智慧模型，提供兩種分析模式：
   • Tabular：
     • 使用DAX語言查詢；DAX以Excel內建function為主，進行大量的資料分析。
     • 在記憶體內執行分析計算。
     • 儘快取常用的資料，不常使用的資料就會丟棄。
   • Multi-Dimension：
     • 即多為度分析，相容現有的OLAP分析模型。
     • 在2008的OLAP可直接無痛升級到2012的Multi-Dimension模型而不用任何的改變。
     • 資料以MDX語言查詢。
   • 兩種類別的模型，彼此都可以用DAX/MDX交互查詢。
   • 要支援BISM，必要軟體為：
     • SQL 2012 商業智慧版或者企業版
     • Sharepoint 2012 企業版
     • Excel 2010
2.  Data Warehouse(DW)建置最佳實務：
   • 資料表不要建立索引。微軟內部效能調校手冊證明索引不適用在大量資料查詢的DW。
   • 資料表之間不要設定關聯。
   • 只要透過SSAS做主鍵值以及欄位間的關聯設定即可。
   • 透過ETL匯入資料前先排序過，就可達到和Clustered Index的效果；或以SELECT INTO ...ORDER BY的方式匯入最佳。
   • 不可做DBCC SHRINKFILE，以避免Fragment更大(Fragment一旦發生便無法回復)。若有Fragment則直接另外建立相同Schema的Table，將資料倒入新資料表內即可。
   • 不做Rollback；資料有錯直接重倒，避免無謂的rollback成本。(這點我存疑，若是有歷史資料(SCD Type II)的該如何處理？或有其他的處理方式？)
   • DW要成功，一開始必須先建立One-Version Truth的命名規則。亦即，同一個欄位名稱在DW內的任何地方都必須只有一種相同的解釋。
   • 以命題為前提，透過Diagram Orgnize設計各種不同的虛擬Data Mart(DM)，組合出所需要的DW內容。換句話說，實體上只要存一份共通Dim/Fact Tables，不用為了不同的DM設計不同的Dim/Fact Tables實體存放雷同的資料。
3. OLAP、PowerPivot、以及BISM Tabular是為相互互補的商業智慧解決方案：
   • OLAP：適用企業組織內經常性、複雜的的商業分析。
   • PowerPivot：
     • 適用一次性或資料來源零碎、不值得建立Cube分析的情況。
     • 可快速建置 OLAP分析。
     • 可發佈到Sharepoint (Excel 2010 + Sharepoint 2010)。
   • BISM Tabular：
     • 適用於部門內的資料分析，建置快速。
     • 可利用伺服器資源(如，記憶體)計算分析。
   • 以上三者皆為資料來源，提供其他前端工具資料分析的來源。
4. 維度設計的原則，是盡可能的將所有可能的Attributes加進來，以達成用最小的維度表涵蓋最大的分析角度，並且增加處理效率。
5. SQL 2012是最後一版支援OLE DB的版本，日後僅支援ADO.NET與ODBC。

最近在玩SSAS（更貼切的說法是我被SSAS玩），費盡千辛萬苦在SSAS上做好Cube後，就先利用Excel 2010的 Pivort Table/Chart Tools來承接Cube分析資料，試圖做到電腦勝人腦的商業智慧報表。但當我勾選某個維度的時候，卻出現以下錯誤訊息：

OLE DB Error: OLE DB or ODBC error: You do not have permission to run 'SP_TRACE_CREATE'.; 42000.

根據google大神指示，必須針對SSAS的執行帳號授與sysadmin的權限，並確認資料來源(Data Source)的執行帳號具有ALTER TRACE的權限。我很快的檢查了一下SSAS服務帳號，的確已經是在sysadmin群組內，那麼剩下的可疑嫌犯應該就是資料來源的帳號權限有問題。我的資料來源並非與SSAS在同一台SQL Server上，而是透過具有db_datareader/db_datawriter的一般性帳號連結到我另外一台SQL Server上建立好的Data Warehouse。又因為Data Source的連線模擬資訊是以「繼承」的方式做連結，因此我猜測必須在Data Warehouse那台伺服器上將該存取帳號授與ALTER TRACE的權限。所以我登入Data Warehouse的SQL Server後，執行下列指令授與該帳號權限：

GRANT ALTER TRACE TO DW_USER;

之後再試著勾選Excel檔案中的維度，就可以正確的將資料Load進來了。

後記：

從Data Warehouse到SSAS的這漫漫長路，對剛踏入這個新知領域的我來說，真的是條很折磨人的修煉。目前雖然可以承接資料，並展現出讓人驚喜的分析結果，但後續的Data Warehouse資料更新效率/頻率、以及Cube自動佈署與處理等議題都還需要花很多功夫來最佳化，我想，苦日子還長的呢。

最近在survey Third-Party的複寫工具，其中XX公司提供了SQL 2008中尚不支援的形態清單，老闆要我檢查一下確保是否有中標的可能。很快的寫了一下script檢查，確認應該是沒有問題才對。以下就提供給大家參考：

SELECT sys.objects.name AS [TableName]
       , sys.columns.name AS [ColumnName]
       , sys.columns.is_filestream AS [IsFileStream]
       , sys.columns.is_xml_document AS [IsXMLDoc]
       , sys.types.name AS [SysTypeName]
       , sys.objects.type
  FROM sys.objects INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
 INNER JOIN sys.types ON sys.columns.system_type_id = sys.types.system_type_id
 WHERE sys.objects.type = 'U'
   AND (sys.columns.is_column_set = 1
        OR sys.columns.is_filestream = 1
        OR sys.columns.is_xml_document = 1
        OR sys.columns.is_sparse = 1
        OR sys.types.name IN ('date'
                             ,'datetime2'
                             ,'datetimeoffset'
                             ,'filestream'
                             ,'geography'
                             ,'geometry'
                             ,'hierarchyid'
                             ,'time'
                              )
       )

 ORDER BY sys.objects.name, sys.columns.name				

上週末系統上線，依照程序做完DDL change後，Source啟動OGG沒問題，但在Target端啟動時卻遇到異常奇怪的錯誤訊息：

OGG-01163 Bad column length (30) specified for column name in table DemoTable, maximum allowable length is 30

找了一下Metalink，僅出現一則關於該錯誤的solution；但內容針對此錯誤所解釋的發生症狀都跟現實狀況不符，使得情況顯得更為棘手。譬如說，此錯誤的出現可能是SQL Server對Column啟用了TDE ，進而導致replicat失敗。但事實上資料庫並沒有啟動TDE功能。除非有人在不知不覺中繞過了我、又能神不知鬼不覺的啟用而不被我發現。

看起來只好回頭查看DDL change的過程是否有漏做或做錯了什麼斬立決的步驟。檢查後發現，最大的不同在於Table Column的順序不同，其餘名稱、型態、長度、與精準度都正常。換句話說，啟動異常的最大的嫌疑犯很可能就是Column的順序不同所造成的。而在Targget端這邊關於DDL方面的參數設定，replicat僅有一個ASSUMETARGETDEFS跟DDL最有牽拖。該參數表示，source端的Table Schema跟Target是一模一樣的，不用再進行例行性檢查以增強效率。好啦，在這樣你情我願的前提之下，OGG就會把交易記錄原原本本的複寫到target table中；更新或者刪除都還好，但就觀察結果來說，insert的指令應該就不會再特別加上對應的欄位，因此target端執行insert時就可能因為欄位順序、欄位長度、型態對應出現missmached，而蹦出這種怪異的錯誤訊息。

解決方式只有一種，就是重新將Target的資料表以source端的table schema為準重新建立一份後，再啟動replicat即可解決。順序如下：  

1. 依照source端的table schema在target端建立一份新的table，譬如說 DemoTable_NEW。
2. 將target端的table，譬如說叫做 DemoTable，匯入新建立的DemoTable_NEW。
3. 建立 DemoTable的索引以及constaints T-SQL script；這邊要注意索引以及constraints名稱要有所變化，避免跟現有的索引以及constraints衝突。
4. rename DemoTable  to  DemoTable_OLD。
5. rename DemoTable_NEW to DemoTable。
6. 執行步驟3的T-SQL
7. 啟動 replicat

網友提問如何查詢在同時間和本交易 (Transaction) 一起存取相同Table的DML，其實很簡單，幾個系統表格兜一兜就可搞定；就算用google大神也應該是一籮筐才對。以下為自hi筆記，當做今天的日記吧 

Oracle GoldenGate (以下簡稱OGG) 對於不同定序的資料庫複寫並沒有問題，但有幾項實作上要注意： 

• 如果source端的定序區分大小寫，target端不區分大小寫時，OGG會將所有的table name 以大寫表示；所以加入chktable時table name也會變成 dbo.CHKTAB；
• 如果source端的定序區分大小寫，target端不區分大小寫時 ，則在target 端mapping table時，要以雙引號把table name 括起來： 
  
  MAP "dbo.myTable", TARGET "dbo.myTable";
  
  
• 如果source端的定序區分大小寫，target 端不區分大小寫時，記得檢查unique key的欄位資料是否符合unique key特性：source端的複寫table可能會建立unique key，在此情況下，target端有可能會遇到傳送過來的記錄不符合限制(一個或一個以上的資料欄位）。這時候就只能自行決定哪筆記錄必須捨棄。

Oralce GoldennGate (以下簡稱OGG)在新增Table後，Extract啟動後常會無預警的ABENDED，查看log也都沒有任何訊息可以參考。這問題非常困擾，一直無法徹底解決，Oracle顧問也是莫可奈何。昨天，在staging上新增了一個table，我膽顫心驚的先關閉data pump跟extract，替該新Table 加trandata後，啟動extract；如預期的，它掛了。不過這次我不再坐以待弊。剛好當天申請了metalink，我抱著姑且一試的心態，試圖在茫茫大海中找出解法；結果老天不負苦心人，竟然給我找到了workaround [metalink: ID 1379180.1]，其解法概述如下： 

• 這問題是個 Bug 12676515，DDL的改變會導致extract啟動失敗；
• 該Bug必須更新到 OGG 11.1.1.1.2以後的版本才有解；
• 在Extract參數檔加入NODDLCHANGEWARNING即可避免這問題的發生。

同事遇到一個有趣的問題；他發現Log File已經釋放出大量的空間，照理說應該可以Shrink下來才對。再者，當下他硬碟空間只剩1GB，Log File長大的速度也挺快的，這問題再不解決可能就是一個P0的棘手問題。好，心臟先Hold住，我請他執行幾個指令確認Log File的空間使用資訊以診斷病情：

• DBCC SQLPERF(LOGSPACE): 確認目前Log File佔用空間的比例
• DBCC LOGINFO: 查看是否有大量的 status  = 2 的虛擬檔案
• DBCC OPENTRAN: 查看是否有Long transaction或者Replication 執行中導致無法釋放Log File
• 確認是否有做Transaction Log File備份

前天被問了個題目：如何讓SQL Server重新啟動的時候發信通知DBA。問題很熟悉，不過當時倒是腦袋一片空白；後來想想應該有什麼辦法可以讓SQL Server在重新啟動時觸發某種程序，以達到發信通知的目的。在網路上參考了一篇論壇文章，說明了只要透過修改伺服器的「Scan for Startup Procs」屬性、並以sp_procoption系統預存程序將發信程序binding到SQL Server啟動後的自動執行作業清單，就可以達到自動發信的目的。而要使資料庫具有發信能力，就須設定DatabaseMail以啟用發信功能。總結來說，我們有幾個步驟完成SQL Server重新啟動時發信通知的功能： 

1. 設定伺服器「Scan for Startup Procs」屬性為true，並重新啟動SQL Server讓該屬性生效。
2. 設定DatabaseMail，讓SQL Server具有發信能力。
3. 在master資料庫內，開發一個以sp_send_dbmail系統預存程序來發信的自訂預存程序。
4. 以sp_procoption系統預存程序將自訂發信程序binding到伺服器啟動程序自動執行的預存程序清單。
5. 重新啟動伺服器測試是否會收到啟動通知。

 

2. 請參考 How to setup Database mail in SQL 2005 設定好您的DatabaseMail。設定好後，記下profile名稱，後面的步驟將會用到。這裡我們以Demo1當做例子。

3. 切換到master資料庫，開發一個結合sp_send_dbmail發信的程序，用以通知SQL Server重新啟動的訊息：

create procedure tm_sp_alert_server_restart
as
begin

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Demo1',

    @recipients = 'myEmail@test.com',

    @body = 'The stored procedure finished successfully.',

    @subject = 'Automated Success Message' ;

end
go  

＠profile_name：在上一步驟中設定好的profile名稱。
＠recipients：收件者電子郵件；若要傳送一人以上，每個人以分號隔開。 
@body：信件內容。
@subject：主旨。

4. 利用 sp_procoption將上步驟中開發的tm_sp_alert_server_restart 預存程序binding到SQL Server重新啟動時自動執行的作業清單中：

exec sp_procoption @ProcName = 'tm_sp_alert_server_restart', @OptionName = 'startup', @OptionValue = 'true'
go 

＠ProcName：自訂的發信預存程序名稱。
@OptionName：這裡我們只能設定startup。 
@OptionValue：true或false；true表示啟用@ProcName為SQL Server啟動時自動執行的預存程序。

5. 重新啟動SQL Server後，若收到下方的信件訊息， 就代表設定已經成功囉：

參考文章：Sending email alert when SQL Server restarts

今日利用一個資料庫備份(假設是 DB01.bak)，在同一個instance上還原成另外一個不同名稱的資料庫(假設是DB02)，原本想繼續透過 sp_change_users_login 將 DB02內的user再mapping到現存的login，讓所有login也可以存取DB02；不過好奇心驅使下我先隨機打開某login的屬性觀察，卻發現其User Mapping頁內，「Users mapped to this login」已經將該login直接對應DB02資料庫內的user；其預設的使用者以及SCHEMA都與DB01相同。而理所當然的，資料庫的還原本來就包含的user的權限設定，因此，該login對應的user在「Database role membership for:DB01(或DB02)」內該的權限設定也與DB01相同。對於login直接mapping新資料庫user的步數，我是挺吃驚的。這項觀察結果，小弟尚未找到相關原理佐證，不過我想應該跟master內儲存的login metainfo有關吧？

這幾天安裝好一台SQL Server 2008 R2，但很快樂地把資料庫Restore過去以及把舊的資料庫attach上去後，才赫然發現伺服器的定序不對；Orz...我的仔細度不夠，竟然受到這樣的懲罰。只好先將資料庫下架，再來改伺服器定序了。

以下是更改伺服器定序的步驟： 

1. Detach 所有的使用者資料庫； 留下master、mode、msdb、以及tempdb等系統資料庫。
2. 準備好的SQL Server 2008 R2 安裝光碟路徑(或者安裝檔的資料夾路徑)，例如 E:\SQL Server 2008 R2 Enterprise。
3. 確認你要變更的定序，這裡我們以Lartin1_General_BIN當作例子。
4. 確認你的instance name；如果是預設的instance，打上MSSQLSERVER即可；如果是Named Instance，譬如說 MyHost\MySQLInstance2，那麼instance name即為 MySQLInstance2。這裡我們以預設的instance name當作例子。
5. 確認你的windwos帳號的帳密資訊；確認這個windows帳號必須是sysadmin的成員。
6. 打開DOS 命令提示字元，切換到第二步中指示的安裝路徑；執行下列指令並等待執行完畢：
   
   Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=myDomain\myAccount /SAPWD=MyPass0rd  /SQLCOLLATION=Lartin1_General_BIN
   
   
7. 執行完畢後，並不會出現成功或失敗的訊息。你必須檢查 Log檔才能知道是否已經成功變更伺服器定序。該Log檔預設位置在C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt。該檔案會先將結果放在第一行。假設你失敗了，Summary.txt的開頭的內容大概如下:

Overall summary:
  Final result:                  Failed: see details below
  Exit code (Decimal):           -2068119551
  Exit facility code:            1211
  Exit error code:               1
  Exit message:                  Failed: see details below
  Start time:                    2011-11-24 15:49:49
  End time:                      2011-11-24 16:00:36
  Requested action:              RebuildDatabase
  Log with failure:              C:\Program Files\Microsoft SQL Server\100\SetupBootstrap\Log\20111124_154924\Detail.txt
  Exception help link: http%3a%2f%2fgo.microsoft.com%2ffwlink%3fLinkId%3d20476%26ProdName%3dMicrosoft%2bSQL%2bServer%26EvtSrc%3dsetup.rll%26EvtID%3d50000%26ProdVer%3d10.50.1600.1%26EvtType%3d0x2E920AA9%400xD3BEBD98%401211%401

 整個程序如果順利完成後，再將資料庫attach回去就完成步驟。

參考資源：設定和變更伺服器定序、 重建系統資料庫

這篇介紹 CDC 的文章講得非常好，我就借花獻佛了。另外快速筆記幾個重要的系統表格跟預儲程序： 

• 啟用CDC需要先在資料庫層級開啟CDC功能，再開啟需要啟用CDC功能的table
  • sys.databases, is_cdc_enabled column：確認資料庫是否有開啟CDC功能之欄位
  • sp_cdc_enable_db：開啟資料庫CDC功能
  • sys.tables, is_tracked_by_cdc column：確認table是否有開啟CDC功能
  • sp_cdc_enable_table：開啟指定Table的CDC功能
    • 啟用table CDC後，會在資料庫內的系統表格內建立相對應的追蹤表格；格式如下：DatabaseName_TableName_CT；有兩個重要欄位可供追蹤資訊辨別：
      • $_operation：標記 DML 的號碼；刪除、新增、更新前、更新後的 operation code 分別為1、2、3、4
      • $_update_mask：一個16進位的數值欄位，但以binary型態儲存；該欄位判斷 table 的哪些欄位開啟CDC功能；預設全部為1，表示所有欄位都開啟CDC；否則開啟欄位的為1，未開啟的為0，組成一個16進位的數值。如果指定部分欄位才啟動CDC，將可在對應的追蹤表格內看到啟用的欄位名稱。只有指定的欄位名稱有變動，才會將記錄寫入追蹤表格內。
• 啟用資料庫CDC功能後，資料庫內將新增一個cdc schema，並另外在該schema下建立系統表格：
  • cdc_captured_columns：所有啟用CDC的欄位清單(預設全部追蹤，但可以指定部分欄位啟用CDC)
  • cdc.change_tables：所有啟用CDC的Table清單；也可用 sp_cdc_help_change_data_capture 來取得
  • cdc.ddl_history：啟用CDC後所有DDL的紀錄
  • cdc.index_columns：記錄啟用CDC的Table相關的索引清單
  • cdc.lsn_time_mapping：LSN 與 Time的對應表
• 啟用資料庫CDC功能時，必須啟動SQL Agent Service，否則啟用CDC會失敗；啟用CDC功能時，自動會新增兩個 Agent Job
  • cdc.DatabaseName_capture：執行Table追蹤功能
    • 執行sp_MScdc_capture_job預儲程序，其內部呼叫sp_cdc_scan執行Log的掃描；如果資料庫已有掃描工作在執行或已經設定交易式複寫，則本工作將不會執行。
  • cdc.DatabasenName_Cleanup：執行sys.sp_MScdc_cleanup_job清除追蹤表格
• 取得某段時間內table的變動LSN記錄:
  • 先取得指定時間區間內的起迄lsn；舉例，取得昨天某Table的LSN 記錄
    • @start_lsn: fn_cdc_map_time_to_lsn('smallest greater than', @begin_time) --@begin_time = getdate()-1
    • @end_lsn：fn_cdc_map_time_to_lsn('largest less than or equal', @end_time) --@end_time  = GETDATE()
      • 第一個參數有四個值：
        • largest less than
        • largest less than or euqal
        • smallest greater than
        • smallest greater than or euqal
    • f取得table在指定的區間內LSN的紀錄：
      • cdc.fn_cdc_get_all_changes_DatabaseName_TableName(@start_lsn, @end_lsn, 'all')
• 預設每三天會執行 sys.sp_cdc_cleanup_change_table，清除所有追蹤的資料。可修改該預設的執行清除時間。目前尚未確認是否會跟transaction log的保留時間有關，但根據觀察，就算備份了Transaction Log 但CDC清除時間尚未到期，也無法將Log檔的大小給Shrink下來，其used%還是很高。如果過了其保留時間，執行清除作業後，並再次執行 Log 檔的備份才可順利進行Shrinkfile的作業。