Tag Archives: AS

列出 AS 角色內的成員

Import-Module sqlps
$a=dir SQLSERVER:\sqlas\mia-sql\sql2\databases\tabularproject2\roles
foreach($r in $a)
{
    $r.Name | Out-File "C:\temp\roles.txt" -Append
    foreach($m in $r.Members)
    {
        "`t" + $m.Name | Out-File "C:\temp\roles.txt" -Append
    }
}

今日推出的 SQL Server Data Tools 16.3,對 Tabular Model 的 Explorer 終於讓人可以一目了然

image

Tabular 授權

試了一會兒 AS/Tabular/DAX 授權

=tbPromotion[Id]=var UserIDs=LOOKUPVALUE(tbUser[ID],[UserName],CUSTOMDATA()) return CALCULATE(VALUES( tbUserPromotion[PromotionID]), SUMMARIZE(‘tbUserPromotion’, tbUserPromotion[PromotionID]),

tbUserPromotion[UserID]=UserIDs)

執行結果似乎是我要的,雖然自己高度存疑,但傳回的資料紀錄又好像都對

一直處在拼積木的過程,搞不清自己在寫什麼,試著放函數到另一個函數的參數位置,一次次地拼著…

似乎現在流行的資料語言不流行語言本身的可讀性

在 SharePoint 上透過 Power View 存取 AS Tabular Model 時要換身分

感謝微軟 Ray 告知的解法,在 SharePoinrt 上要用 Report Data Source(Data Source Type 選擇 Microsoft BI Semantic Model for Power View) 而非 BI Semantic Model Connection

image

詳細作法參考以下網址:

http://whitepages.unlimitedviz.com/2012/04/fixing-access-errors-when-creating-a-bism-connection-for-powerview/

讓 RS 可以存取 AS 的 Tabular Model

參考以下的網址

https://www.simple-talk.com/sql/database-administration/using-dax-to-create-ssrs-reports-the-basics/

補充一點,若要設定 DAX,也可以直接點 Expression 按鈕,而不必如文中叫起 Query Designer

image

image

我的 Sample Code

關於 Dimension Processing 的屬性

就上課朋友的問題所做的搜尋如下:

image

http://blogs.msdn.com/b/sqlcat/archive/2009/04/01/using-processinggroup-dimension-property-option-bytable-vs-byattribute-may-error-with-string-keys.aspx

設定 Processing Attribute 時,是逐 Attribute 下 Select distinct,最後在一次 Select Distinct Key + 所有的 Attribute:耗時,省 Memory

還是依次 Select * From Table:耗 Memory

 

image

https://svangasql.wordpress.com/2011/11/21/ssas-processing-mode-lazy-aggregations/

設定全部 Processing 完才讓 User 使用:第一次回應慢,接下來快

Processing 完 Data 就讓 User 使用,剩下的 Index 和 Aggregate 之後運算:若查到時還沒算,會需要等待

 

image

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0120ba65-9b5a-463c-88f0-d7af83f559f0/processingpriority

若是 Serial Process,則 Priority 高的先做

安裝完 AS 後切換 Tabular 和 MultiDimensional 模式

感謝巧玫提供這有趣的網址:

http://caryhsu.blogspot.tw/2013/04/analysis-service.html

http://cathydumas.com/2012/04/23/changing-an-analysis-services-instance-to-tabular-mode/

透過 PowerShell 部署 AS DB 和 rdl 到 SharePoint

SQL 制式教材內,透過 PowerShell 部署 AS DB 和 rdl 到 SharePoint 的 Script 可以參考:

#執行 xmla 部署 AS DB

Import-module sqlps
Import-module sqlascmdlets
Invoke-ASCmd -Inputfile:"$env:SUBDIR\SetupFiles\SetupSSAS.xmla" -Server:"localhost"

 

#部署  rdl 到 SharePoint

Add-PSSnapin Microsoft.SharePoint.PowerShell
$siteName = "http://localhost/sites/adventureworks"
$docLibName = "Reports"

$web = Get-SPWeb $siteName
$docLib = $web.Lists[$docLibName]
$rootFolder = $docLib.RootFolder
$folderURL = $rootFolder.Url
$folder = $web.GetFolder($folderURL)
foreach ($file in $folder.Files) {
  $docLib.Items.DeleteItemById($file.Item.Id)
}

$subs = @()

foreach($subfolder in $folder.SubFolders) {
  if ($subfolder.Name -ne "Forms") {
      $subs = $subs + $subfolder.Url
  }
}

foreach ($url in $subs) {
  $folder.SubFolders.Delete($url)
}

$folderName = "$env:SUBDIR\SetupFiles\Reports"
$files =  ([System.IO.DirectoryInfo] (Get-Item $folderName)).GetFiles() | ForEach-Object {
    $fileStream = ([System.IO.FileInfo] (Get-Item $_.FullName)).OpenRead()
    $content = New-Object byte[] $fileStream.Length
    $fileStream.Read($content, 0, [int]$fileStream.Length);
    $fileStream.Close()
    $spFile = $rootFolder.Files.Add($rootFolder.Url + "/" + $_.Name, $content, $true)
}

AS Tabular Model 的輔助小工具

比較資料庫、部署、合併程式碼、與 TFS 整合:BISM Normalizer

輔助 BIDS/SSDT 開發 AS 模型,例如為 Tabular 建立 Drillthrough Action(MVA MVA — Implementing Tabular Model Solutions 第四單元透過 DAX Query 回傳 Dataset 的 Action,提供客製化 drillthrough):BIDS Helper

DAX Query 的語法編輯器:DAX Studio

討論 Tabular Model 各種問題的設計模式:DAX patterns

參考資源:MVA — Implementing Tabular Model Solutions

AS Tabular 的 Row Level 的 Role Base Model Security

Tabular Model 設計如下:

image

image

image

角色定義如下:

image

image

以 AS 的 Administrator 角色透過 Connection String 模擬 Role1 的結果

image

Provider=MSOLAP.6;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=TabularProject1_Byron_6f652aee-a22e-41fb-8df2-d60c4b278c0e;Data Source=SQL2014\I2;MDX Compatibility=1;Roles=Role1;Safety Options=2;MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue

 

直接以 C 帳號登入的結果

image

搭配 RS 時,透過動態產生連接字串:

image

其內容定義:

image

=IIF(Parameters!Role.Value="None","Provider=MSOLAP.6;Data Source=.\i2;Initial Catalog=TabularProject1","Provider=MSOLAP.6;Data Source=.\i2;Initial Catalog=TabularProject1;Role=" + Parameters!Role.Value)

報表參數定義:

image

報表設計如下:

image

其執行結果如下:

image

 

另外,有點奇怪的是:

在設計報表時,若資料來源選用 Analysis Services

image

則查詢一定要用 MDX 的產生器,因此不能寫 DAX

image

但若資料來源採用 OLE DB 的 Microsoft OLE DB Provider for Analysis Services 12.0

image

則可以自行編輯 DAX

image

由於 DirectQuery 只支援 DAX,所以若要採用 DirectQuery Mode 而非 InMemory Mode,且要利用 RS 呈現,或可如此撰寫 DAX

上述範例專案與報表:

https://onedrive.live.com/redir?resid=BF14192BD27975CB!56936&authkey=!AEM6dSaoa70pNmk&ithint=file%2czip

參考資料:

MVA — Implementing Tabular Model Solutions

Use EffectiveUserName with Excel Services (SharePoint Server 2013)