Tag Archives: AS

AS 2017 Tabular Model 終於有 Column Level Security

MS 稱之為 Object-level security https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/object-level-security

可惜目前只能 Hard coding TMSL JSON

image

以角色 America 模擬檢視:

image

不屬於這個角色時,是可以看到 City 和 Region 的

image

廣告

列出 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