Power BI and Reporting Perspectives: Auditing SSAS; Column vs. measure subfolders; Six Sigma Control Charts; Tabular Model Schema Reference
This week's Power BI and reporting perspectives include when to use column vs. measure subfolders, Six Sigma Control Charts and more.
Auditing SQL Server Analysis Services in Azure
Kasper Jonge, writing on the Kasper on BI blog, investigated the process of auditing a SQL Server Analysis Services (SSAS) server in Azure now that SSAS has been shifted away from on-premises in many cases. Working on-premises, Kasper was used to writing code to run XEvents or capturing profile traces to gain insight on the queries going to the database. In this case, he used Azure Log Analytics, which can stream events to Event Hub or a storage account but in this situation routed to his Log Analytics workspace. After logging the queries, he exported the results to Power BI Desktop as M script, which can create a visual of the hourly query total.
Column vs. Measure Sub Folders
MVP Matt Allington, writing on the Excelerator BI blog, revisited the question of how to best use measure tables and measure subfolders in Power BI and recommended column subfolders instead of measure subfolders. According to Allington, it's best for users to put measures in the table where the data comes from and then create a subfolder for all the columns in the table. Taking this approach means that subfolders will be collapsed by default and are visible without the need to expand the subfolder. Additionally, when new measures are created they appear automatically in the list.
Six Sigma Control Charts
Also on the Excelerator BI blog, Allington addressed questions from his online and in-person trainings about how to setup Six Sigma Control Charts. Starting out, he created a DAX formula—in this case for average sales by day—carried out a few additional calculations and then used a slicer to filter control limits data in a combo chart. He wrote:
The idea is to create an interactive dynamic chart that displays sales distribution over the selected time period. The part of the columns below LCL, above UCL, and between LCL and UCL are to be displayed with different colours. To get this, I had segmented the columns with the following DAX formulas.
FREE Membership Required to View Full Content:
Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more. You can also receive periodic email newsletters with the latest relevant articles and content updates.
Learn more about us hereor login