2018 年 11 月 22 日 – 00:20:26
2018 年 08 月 18 日 – 00:22:55
取自 The Definitive Guide to DAX Ch13 The VertiPaq engine
Number of cores
VertiPaq splits execution on multiple threads only when the table involved has multiple segments. Each segment contains 8 million rows by default (1 million on Power Pivot). If you have eight cores, you will not see all of them involved in a query unless you have at least 64 million rows.
For these reasons, scalability over multiple cores is effective only for very large tables. Raising the number of cores will improve performance for a single query only when it hits a large table (200 million of rows or more). In terms of scalability (number of concurrent users), a higher number of cores might not improve performance if users access the same tables (they would contend access to shared RAM). A better way to increase the number of concurrent users is to use more servers in a load balancing configuration.
The best practice is to get the maximum number of cores you can have on a single socket, getting the highest clock rate available. It is not good having two or more sockets on the same server. Analysis Services Tabular does not recognize the NUMA architecture, which splits memory between different sockets. NUMA requires a more expensive intersocket communication whenever a thread running on a socket accesses memory allocated by another socket—you can find more details about NUMA architecture in Hardware Sizing a Tabular Solution (SQL Server Analysis Services) at http://msdn.microsoft.com/en-us/library/jj874401.aspx.
Memory size
You have to store the entire database in memory. You also need RAM to execute process operations (unless you use a separate process server) and to execute queries. Usually optimized queries do not have a high request of RAM, but a single query can materialize temporary tables that could be very large (database tables have a high compression rate, whereas materialization of intermediate tables during a single query generates uncompressed data).
Having enough memory only guarantees that your queries will end returning a result, but increasing available RAM does not produce any performance improvement. Cache used by Tabular does not increase just because of more RAM available. However, a condition of low available memory might affect query performance in a negative way if the server starts paging data. You should simply have enough memory to store all the data of your database and to avoid materialization during query execution.
Disk I/O and paging
You should not allocate budget on storage I/O for Analysis Services Tabular. This is very different from Multidimensional, where random I/O operation on disk occurs very frequently, especially in certain measures. In Tabular, there are no direct storage I/O operations during a query. The only event when this might happen is when you have a low memory condition. However, it is less expensive and more effective to provide more RAM to a server than trying to improve performance by increasing storage I/O throughput when you have a systematic paging caused by low memory available.
Conclusions
You should measure performance before choosing the hardware for SSAS Tabular. It is common to observe a server running twice as slow as a development workstation, even if the server is a very new one. This is because a server designed to be scalable (especially for virtual machines) does not usually perform very well for activities made by a single thread. However, this type of workload is very common in VertiPaq. You will need time and numbers (do your benchmark) to convince your company that a “standard server” could be the weak point of the entire BI solution. Nevertheless, before convincing anybody else, keep in mind that you need to convince yourself
2018 年 06 月 30 日 – 06:55:43
https://docs.microsoft.com/zh-tw/sql/analysis-services/multidimensional-models/deploy-model-solutions-with-the-deployment-utility?view=sql-server-2017
Analysis Services 部署精靈現在是隨 SSMS，所以預設要在以下目錄找
C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio
檔名是 Microsoft.AnalysisServices.Deployment.exe
2017 年 10 月 26 日 – 00:46:44
感謝微軟 Egg 告知，Power BI Desktop 是在背後起一個 AS 服務，以如下的 Power BI Desktop 為例：
透過 PowerShell 查該 msmdsrv 的 Processid 後，透過 netstat –o 查聽的 port
而後透過 SSMS 連結 AS
P
就可以透過物件總管觀察該 Tabular Model
透過 SSDT 從 Server 讀回該 Model 定義會有如下的錯誤
可以產生 TMSL Script
而後再到 AS Tabular 貼上語法執行時，因為會多出 .tables[1].isPrivate、 model.tables[2].columns[3].variations…等屬性而無法部署到獨立的 AS 上，得到的錯誤訊息如下：
正在執行查詢 …
JSON DDL 要求失敗，並出現下列錯誤: Unrecognized JSON property: isPrivate. Check path ‘model.tables[1].isPrivate’, line 251, position 25.。
執行完成
2017 年 10 月 06 日 – 04:04:30
MS 稱之為 Object-level security https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/object-level-security
可惜目前只能 Hard coding TMSL JSON
以角色 America 模擬檢視：
不屬於這個角色時，是可以看到 City 和 Region 的
2017 年 04 月 22 日 – 07:20:48
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
}
}
2016 年 06 月 26 日 – 07:17:49
試了一會兒 AS/Tabular/DAX 授權
=tbPromotion[Id]=var UserIDs=LOOKUPVALUE(tbUser[ID],[UserName],CUSTOMDATA()) return CALCULATE(VALUES( tbUserPromotion[PromotionID]), SUMMARIZE(‘tbUserPromotion’, tbUserPromotion[PromotionID]),
tbUserPromotion[UserID]=UserIDs)
執行結果似乎是我要的，雖然自己高度存疑，但傳回的資料紀錄又好像都對
一直處在拼積木的過程，搞不清自己在寫什麼，試著放函數到另一個函數的參數位置，一次次地拼著…
似乎現在流行的資料語言不流行語言本身的可讀性
2015 年 10 月 07 日 – 02:25:32
感謝微軟 Ray 告知的解法，在 SharePoinrt 上要用 Report Data Source(Data Source Type 選擇 Microsoft BI Semantic Model for Power View) 而非 BI Semantic Model Connection
詳細作法參考以下網址：
http://whitepages.unlimitedviz.com/2012/04/fixing-access-errors-when-creating-a-bism-connection-for-powerview/
偶有所得 