如何建立Power View報表 - 以Tabular Mode為例

        前面的幾篇文章已有介紹到多種設計PowerView的方式，再來本篇，我們來介紹如何連結到SSAS - Tabular Model(表格模式)的部份，設定方式如下：

1、請先下載下列的範例資料庫。

Database Name：AdventureWorksDW2012 Data File
URL：http://msftdbprodsamples.codeplex.com/downloads/get/165405

2、下載專案檔案。
Project Name：AdventureWorks Internet Sales Tabular Model SQL Server 2012
URL：http://msftdbprodsamples.codeplex.com/downloads/get/353144

專案完整的制作方式說明，您可以參考下列的網址：
http://msdn.microsoft.com/en-us/library/hh231691.aspx

3、請先確認您目前安裝的SQL Server Analysis Service為表格模式(Tabular Mode)。
確認方式您可以透過下列的兩種方式進行。

3-1 透過SQL Server Management Studio連線到SSAS時，您可以透過連結的圖示來判斷模式為何。

表格模式(Tabular Mode)

多維度模式(Multidimensional Mode)

3-2 開啟SSAS的設定檔，藉以判斷目前的安裝模式為何。

路徑：
C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config\msmdsrv.ini

確認DeploymentMode的參數，數值為0時，代表為多維度模式(Multidimensional Mode)，數值為2時，代表為表格模式(Tabular Mode)。

4、開啟專案後，選擇部署，即可完成SSAS環境的建置。

5、 登入SharePoint的前端網站後，請點選 [文件] -> [新增文件] -> [BI語意模型連接]

6、 請在下列輸入相對應的資訊，然後點選確定。

7、 建立 [BI 語意模型連接] 後，請再點選 [ 建立 Power View 報表 ]。

8、 此時即會開啟Power View的開發環境，然後請依序點選下列的項目，即可完成報表的設計。
8-1 請點選 [Date] -> Calendar Year 與 Month
8-2 請點選 Internet Sales -> Sales Amount
8-3 請點選 [設計] -> 線條

其他參考文章：
如何在Excel 2013中使用PowerView
http://caryhsu.blogspot.tw/2012/12/excel-2013powerview.html
如何建立PowerView的報表
http://caryhsu.blogspot.tw/2013/03/powerview.html


關鍵字：SSAS、Tabular Model、PowerView

如何切換變更Analysis Service的安裝模式

        以往SQL Server Analysis Service中主要是以多維度(Multidimensional Models)的模式方式進行，但是在SQL Server 2012之後，推出了表格模式(Tabular Models)的方式提供給使用者進行選擇，由其是以當你需要使用PowerView時，到目前為止(註1)，SSAS端只能透過表格模式的方式當成資料來源端。

由於最近需要測試PowerView 連結到SSAS的部份，但可惜我當初安裝時是以多維度模式進行安裝，所以PowerView無法連結，後來找到一個方法，可以透過下列的方式，將你的SSAS進行模式的切換。

PS：此篇所介紹的切換方式只適合於剛安裝完成，而且沒有進行任何的專案部署，否則你會遇到在表格模式中無法針對以多維度模式部署的資料庫，再請多多注意。

註一：
由於目前在PowerView的部份已有提供連結到多維度模式(Multidimensional Models)的方式，但由於目前只到CTP的版本，大家可以參考下列的連結。

Power View for Multidimensional Models - Preview
http://blogs.msdn.com/b/analysisservices/archive/2012/11/29/power-view-for-multidimensional-models-preview.aspx


1、請先確認目前SSAS安裝的模式為何。

1-1 當你連結到SSAS時，如果開頭的圖示為下列的情況時，代表你目前是多維度模式(Multidimensional Models)。

1-2 當你連結到SSAS時，如果開頭的圖示為下列的情況時，代表你目前是表格模式(Tabular Models)。

2、開啟下列的檔案進行模式的變更‧

2-1 檔案路徑
C:\Program Files\Microsoft SQL Server\MSAS11.TABULAR\OLAP\Config\msmdsrv.ini

2-2 打開上述的檔案，你會看到DeploymentMode的區塊，0代表為多維度模式，2為表格模式。

2-3 修改完成後，再請重新啟動SSAS即可。

3、當您連結到SSAS，開啟資料庫時，如果要進行開啟或刪除時，遇到下列的錯誤，代表你的資料庫與你目前的模式不同所造成，要解決下列的問題，很簡單，只要將模式先切回原本的模式後，再刪除已部署的資料庫，然後再切換即可。

錯誤訊息：
無法在引擎中建立VertiPaq結構描述 'Analysis Services Tutoial'，因為它已經存在。
從檔案 '\\?\C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data\Analysis Services Tutorial.0.db.xml' 載入 Analysis Service Tutorial database 時發生錯誤。
(Microsoft.AnalysisServices.Xmla)


參考連結：
Changing an Analysis Services instance to tabular mode
http://cathydumas.com/2012/04/23/changing-an-analysis-services-instance-to-tabular-mode/

關鍵字：SQL Server Analysis Service、Multidimensional Models、Tabular Models、PowerView

如何建立PowerView的報表

        在前面的幾篇已有完整的介紹到如何進行PowerView的環境建置與在Excel中進行報表設計的方式，所以本篇再來介紹如何透過PowerPivot連線到SQL Server進行PowerView報表的設計。

參考連結：
PowerView環境安裝與建置
http://caryhsu.blogspot.tw/2012/09/powerview.html
如何在Excel 2013中使用PowerView
http://caryhsu.blogspot.tw/2012/12/excel-2013powerview.html


PowerView環境安裝與建置

先決條件：
1、如果您的Excel的版本為2013以下時，需請您先行安裝下列的檔案。
        PowerPivot for Excel 安裝 (2010)
        http://msdn.microsoft.com/zh-tw/library/ee210599.aspx
2、範例資料庫 - Northwind
        SQL Server 2000 Sample Databases
        http://archive.msdn.microsoft.com/northwind/Release/ProjectReleases.aspx?ReleaseId=1401


Demo說明：
由於PowerView目前只能透過Excel與直接連接到Analysis Service的Tabular Mode，所以本Demo我將介紹如何透過Excel連線到SQL Server，然後設計一張客戶國家別的分佈統計圖。


設定流程：
1、建立Excel檔案連結至SQL Server
1-1 選擇 [資料] -> [從其他來源] -> [從SQL Server]

1-2 輸入您的伺服器名稱與登入的資訊

1-3 選擇 [Northwind] 資料庫，並先只選擇 [Customers] 的資料表即可。

1-4 確認連線資訊

1-5 匯入資料的部份，請選擇放至在 [新工作表] 即可。

1-6 確認資料已匯入至Excel中。

2、建立PowerPivot連線
2-1 選擇 [PowerPivot] -> [建立連結資料表]

2-2 此時他會將你目前選擇的工作表建立PowerPivot的連線

3、建立PowerView報表
3-1 請先將剛剛建立的Excel上傳至SharePoint的網站上

3-2 選擇Excel的檔案

3-3 確認是否更換excel的顯示名稱

3-4 上傳完成後，請點選 [文件] -> [新增文件] -> [BI語意模型連接]

3-5 請輸入此連接的名稱，而活頁簿URL，請輸入剛剛上傳的Excel名稱。

PS:預設路徑為：http://server-name/share%20Documents/powerview_demo.xlsx


3-6 建立完成後，請選擇 [建立 Power View報表]

4、設計PowerView報表
4-1 開啟PowerView的報表後，右半邊即會載入您在Excel中有設定PowerPivot連線的資料表

4-2 請勾選 [Country]與[CustomerID]，然後在下方欄位的部份，請將 [CustomerID]變更為 [計數 (不是空白)]

4-3 點選 [設計] -> [線條]

4-4 此時即可將剛剛的資訊透過線條圖的方式呈現。

關鍵字：SQL Server、Reporting Service、PowerView

如何結合Database Mirroring與Log Shipping

         在我前面多篇的文章中已有介紹到多種HA(High Availability Solutions)的架設方式，而其實最簡單也是最多人入門使用的，就是Log Shipping與Database Mirroring，但由於Database Mirroring不像AlwaysOn可以擁有多個複本，只能有一個鏡射節點，所以這時候我們就可以透過Database Mirroring與Log Shipping的整合，藉以達到多個複本，也可以達到同地與異地的備援方案。

前導文章：

如何建立 Log Shipping
http://caryhsu.blogspot.com/2012/02/log-shipping.html
SQL Server - 如何建立 Database Mirroring
http://caryhsu.blogspot.com/2011/12/sql-server-database-mirroring.html


相關的架設方式，其實可以參考MSDN上的說明，但可惜的是官網上說的太過於簡單，所以我才整理這篇文章來加以說明，希望大家可以更清楚的了解到如何整合Database Mirroring與Log Shipping.


架構說明：
1、Database Mirroring：
1-1 建議使用高可用性的架構(High-Safety Mode)。
1-2 建議有一台監控伺服器(Witness Server)

2、Lop Shipping：
2-1 不一定要有監控伺服器(Witness Server)，可以透過手動進行Log Shipping的Failover。
2-2 備份的檔案必須放在第三台上面，也就是檔案放在一台獨立的主機上。
2-3 還原交易記錄請選擇不復原模式。

設定方式：
1、請先參考之前的文章進行架設，但整合上有些注意事項，詳細的部份，請一定要依照架構說明的部份進行。

SQL Server - 如何建立 Database Mirroring
http://caryhsu.blogspot.com/2011/12/sql-server-database-mirroring.html

2、設定Log Shipping。
2-1 指定備份的位址，此時我將備份的位置指定到備份主機上的目錄，再次提醒，此位置必須讓Database Mirroring與Log Shipping的主機都要能有讀寫的權限。

PS：由於資料的限制，所以我先將檔案備份的位址放在AD的主機上，其實應該要在一台獨立的主機上，而不是放在AD上，以免影響AD主機的運作。


2-2 選擇加入Log Shipping的Secondary主機後，由於我在Secondary沒有這個資料庫的存在，所以我選擇第一項進行。

2-3 切換到 [複製檔案] 的頁次，輸入Log Shipping中Secondary上的分享路徑上。

2-4 切換到 [還原交易記錄] 的頁次後，此部份請選擇 [不復原模式] ，藉以避免在復原的情況下，其他使用者的存取，造成復原的失敗。

2-5 設定完成後，請將此設定輸出成指令檔，後續上會使用到。

2-6 建立完成後，請先確認各個Job執行上應該都能正常的執行。

2-6-1 Primary上的 Job清單
LSBackup_Northwind

2-6-2 Secondary上的Job清單
a. LSAlert_CARY-TEST
b. LSCopy_WIN-2008R2-1_Northwind
c. LSRestore_WIN2008R2-1_Northwind


3、整合Database Mirroring與Log Shipping
3-1 請在Database Mirroring上進行手動的Failover，將節點切換到Mirror的機器上。
3-2 請在原本是Mirror的機器上(目前為Principal)執行之前儲存的Script。

PS:
a. Script的部份分成兩大區塊，上半部是在(Primary)的主機上執行，而下半部是在Secondary的主機上執行，但請注意，我們在目前Principal(之前的Mirror)的機器上只需執行上半部即可。
b. 基本上你另存的語法不需作任何的修改，只需執行在Principal (之前的Mirror)的主機上即可。
c. 由於Log Shipping的GUI介面只能指定到一個Primary主機，所以一定要透過語法的方式進行。


語法內容 -- 只截取上半部
-- 在主要伺服器端執行下列陳述式，以設定記錄傳送
-- 針對資料庫 [WIN-2008R2-1].[Northwind]
-- 指令碼需要在主要伺服器端的 [msdb] 資料庫內容中執行。
-------------------------------------------------------------------------------------
-- 加入記錄傳送組態

-- ****** 開始: 要在主要伺服器端執行的指令碼: [WIN-2008R2-1] ******


DECLARE @LS_BackupJobId AS uniqueidentifier
DECLARE @LS_PrimaryId AS uniqueidentifier
DECLARE @SP_Add_RetCode As int


EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
@database = N'Northwind'
,@backup_directory = N'\\win-cary-ad\log_backup'
,@backup_share = N'\\win-cary-ad\log_backup'
,@backup_job_name = N'LSBackup_Northwind'
,@backup_retention_period = 4320
,@backup_compression = 2
,@backup_threshold = 60
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@backup_job_id = @LS_BackupJobId OUTPUT
,@primary_id = @LS_PrimaryId OUTPUT
,@overwrite = 1


IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
BEGIN

DECLARE @LS_BackUpScheduleUID As uniqueidentifier
DECLARE @LS_BackUpScheduleID AS int


EXEC msdb.dbo.sp_add_schedule
@schedule_name =N'LSBackupSchedule_WIN-2008R2-11'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20130312
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_BackUpScheduleUID OUTPUT
,@schedule_id = @LS_BackUpScheduleID OUTPUT

EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_BackupJobId
,@schedule_id = @LS_BackUpScheduleID

EXEC msdb.dbo.sp_update_job
@job_id = @LS_BackupJobId
,@enabled = 1


END


EXEC master.dbo.sp_add_log_shipping_alert_job

EXEC master.dbo.sp_add_log_shipping_primary_secondary
@primary_database = N'Northwind'
,@secondary_server = N'cary-test'
,@secondary_database = N'Northwind'
,@overwrite = 1

-- ****** 結束: 要在主要伺服器端執行的指令碼: [WIN-2008R2-1]  ******

3-3 此時請再執行各個Log Shipping上的Job，藉以確認是否正常運作，如此即完成此兩個功能的整合。


參考網址：
Database Mirroring and Log Shipping (SQL Server)
http://msdn.microsoft.com/en-us/library/ms187016.aspx


關鍵字：Database Mirroring、Log Shipping、Database Mirroring and Log Shipping Working Together、Setup database mirroring and log shipping together

資料庫使用者最小權限設定

        使用者權限的部份往往因為方便性，常常看到許多的管理者都直接使用SA的帳號進行連線，但萬一系統遭受到如SQL Injection等入侵攻擊時，連帶的直接就讓入侵者直接取得資料庫的最高權限，讓整個資料庫系統停止服務。

有鑑於此本章我們就來介紹如何設定一個最小權限的使用者，藉以提供前端的程式進行連線，一來可以提供更佳的防護，二來更可以社絕資料庫間相互的影嚮所造成的損失。


操作流程：
1、開啟Management Studio。
2、選擇 [Security] -> [Logins] -> [New Login]

3、輸入使用者資訊

3-1 帳號的部份建議使用  Windows Authentication。
3-2 Default Database(預設資料庫)請不要使用預設的 master ，請選擇一個特定的資料庫，因為通常前端的程式如果有存取master，代表這些可能都是可疑的行為，所以指定使用者到一個特定的資料庫，藉以避免監控程式的誤判。

4、Server Roles的部份請不需勾選，尤其是sysadmin，請不要勾選。

5、此部份請勾選使用者特定的資料庫。

5-1 Database Role請先取消 [db_owner] 的勾選。
5-2 因為一般應用程式會針對資料庫進行Insert、Update、Delete，所以請勾選 [db_datareader]、[db_datawriter]即可。

6、按下確定後，會出現下列的錯誤訊息，其實這訊息主要是因為在1-5的步驟中取消 [db_owner]的部份，所以才會出現這個訊息，你可以忽略他。

針對這個問題你可以將操作的語法輸出後，你就可以看到語法的最後因為要針對 db_onwer進行成員的設定，所以才會有下列的錯誤訊息了，點選 ok 後，帳號即建立完成。

透過上面的方式建立出來的帳號即會只使用最小的權限存取資料庫，如此一來就算真的因為前端的程式設計不佳造成SQL Injection的問題被入侵，因為權限最小化的關係，入侵者也頂多只能針對此資料庫進行存取，無法進一步的進行其他資料庫或系統的破壞，藉以將傷害降到最低。


錯誤訊息：
Drop member failed for DatabaseRole 'db_owner'. (Microsoft.SqlServer.Smo)

An Exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

An object or column name is missing or empty. For SELECT INTO statements, verify each column
has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not
allowed. Change the alias to a valid name. (Microsoft SQL Server, Error:1038)



問題原因：
SE [AdventureWorks2012]
GO
ALTER ROLE [db_owner] DROP MEMBER []
GO

PS：因為取消勾選時，工具的介面在產生語法上的錯誤，所以造成這種情況。

關鍵字：
最小權限設定、minimum permission、Minimum SQL login permissions