DAX_Studio_Eng

Introducing DAX Studio Last update: Jan-2024 Fabric CAT: Eiki Sui

DAX Studio External tools that connect to Power BI or an Analysis Service instance of Excel to obtain and analyze various metadata information

DAX Studio Developer Darren Gosbell SENIOR PROGRAM MANAGER Joined MS in June-2022, Power BI CAT in Australia; developed DAX Studio since 2011 to date As of 7/1/2022, DAX Studio has been downloaded more than 134,000 times in its existing version https://daxstudio.org/ SLQBI Marco Russo & Alberto Ferrari Official site (English) https://daxstudio.org/doc umentation/ How to use (Japanese -Qiita) https://bit.ly/3bGtDoK Pioneer in Microsoft BI technology. They run SQLBI, which provides technical know-how on DAX / Data Model, code checking and UI design support for DAX Studio The Definitive Guide to DAX Is the Bible for all DAX students to refer to

• https://daxstudio.org/
• https://daxstudio.org/documentation/
• https://bit.ly/3bGtDoK

External Tools Concept of External Tools • Mostly done by community work • Mostly open source Tech-tech Blog (Japanese) https://marshal115.hatenablo g.com/entry/2022/05/27/181 736 External tools (Official) https://docs.microsoft.com/ power-bi/transformmodel/desktop-externaltools • Main purpose is to improve efficiency in Power BI • DAX Studio is one of the most important external tools that have existed for a long time (before the advent of Power BI) • Analyze in Excel, Bravo for Power BI、Tabular Editor 2, Tabular Editor 3, etc. are well known

• https://marshal115.hatenablog.com/entry/2022/05/27/181736
• https://docs.microsoft.com/power-bi/transform-model/desktop-external-tools
• https://daxstudio.org/documentation/features/
• https://www.sqlbi.com/tools/analyze-in-excel-for-power-bi-desktop/
• https://bravo.bi/
• https://github.com/TabularEditor/TabularEditor/releases
• https://tabulareditor.com/

Using DAX Studio Launch DAX Studio

Connection options Connection Tools 1. Power BI Desktop 2. Excel Power Pivot ◆ Since it is usually used with Power BI, the following discussion will be basically based on Power BI. 3. SSDT※1 4. Analysis Service 5. Azure Analysis Service 6. Power BI XLMA Endpoint※2 ※1 Development tool to design and deploy SQL Server content types ※2 Power BI Premium or PPU license required Details of connection options (DAX Studio Official Doc) https://daxstudio.org/tutorials/getting-connected/

• https://docs.microsoft.com/power-bi/fundamentals/desktop-getting-started
• https://support.microsoft.com/office/power-pivot-概要と学習-f9001958-7901-4caa-ad80-028a6d2432ed
• https://docs.microsoft.com/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver16
• https://docs.microsoft.com/analysis-services/analysis-services-overview?view=sql-analysis-services-2022
• https://docs.microsoft.com/azure/analysis-services/analysis-services-overview
• https://docs.microsoft.com/power-bi/enterprise/service-premium-connect-tools
• https://daxstudio.org/tutorials/getting-connected/

Installing & Starting DAX Studio 1. Install DAX Studio https://daxstudio.org/ 2. Launch of DAX Studio Launch from Power BI Launch from Excel Launch from DAX Studio

• https://daxstudio.org/

Launch from Power BI Launch from Power BI 1. Open the target Power BI Desktop 2. External Tools > DAX Studio ◆ When DAX Studio is installed, it appears as an external tool for Power BI Desktop ◆ Please use the latest versions of DAX Studio and Power BI Desktop when possible ※ Assuming data model is available

Launch from Excel Launch from Excel 1. Launch Excel (Power Pivot Model required) 2. Add-Ins > DAX Studio ◆ When connecting DAX Studio to an Excel data model, always start Excel first

Launch from DAX Studio ◆ When launching from DAX Studio, perform a search from the search window ◆ Make a connection to the Analysis Service instance of Power BI Desktop ◆ Since Excel has its own instance, connection is not possible when launching from DAX Studio. DAX Studio must be launched from an add-in in Excel (because the network port is different) Launch from DAX Studio 1. Launch DAX Studio

Key practical features with DAX Studio Functions that cannot or are difficult to achieve with Power BI Desktop

Key features offered by DAX Studio Metadata Information Measuring Query Performance Query Editor Feature Data Export Feature Features (Official - English) https://daxstudio.org/docum entation/features/ Query Builder DAX Formatting Feature

• https://daxstudio.org/documentation/features/

DAX Studio features Metadata Information Official site (English): https://daxstudio.org/documentation/features/model-metrics/

• https://daxstudio.org/documentation/features/model-metrics/

DAX Studio features Measuring Query Performance Optimizing DAX（Japanese） https://marshal115.hatenablog.com/entry/2022/02/24/181416

• https://marshal115.hatenablog.com/entry/2022/02/24/181416

DAX Studio features Query Editor Feature Official site (English) https://daxstudio.org/tutorials/writing-dax-queries/

• https://daxstudio.org/tutorials/writing-dax-queries/

DAX Studio features Data Export Feature ◆ General export ◆ Bulk export Blog in Japanese: https://marshal115.hatenablog.com/entry/2020/06/06/182918

• https://marshal115.hatenablog.com/entry/2020/06/06/182918

DAX Studio features Query Builder ◆ Query Builder allows DAX queries to be generated with a simple Drag & Drop ◆ As a use case, output raw data with generated query for reuse Official site (English): https://daxstudio.org/documentation/ features/query-builder/

• https://daxstudio.org/documentation/features/query-builder/

DAX Studio features DAX Formatting Feature Note: DAX Studio will send request to DAX Formatter so network is required https://www.daxformatter.com/

• https://www.daxformatter.com/

DAX Studio Notes DAX Studio is not for beginners, but a tool for intermediate to developers. Beginners can also use it if they feel comfortable

Practical examples Practical use cases

Example of use: Displaying metadata information Confirmation of overall model Consider optimization needs by understanding model metadata information Total Size is usually larger than the PBIX file E.g.: Is the number of tables = 7 and the number of columns = 61 the optimal number?

Example of use: Displaying metadata information Check the details of the model Breakdown from overview (tables) to details (columns) Are these columns with high cardinality necessary? Wouldn't deleting them reduce the DB size?

Example of use: Measuring query performance Query optimization 40x performance improvement

Example of use: Measuring query performance Check the specs of your PC Tech-tech blog (Japanese) Reference ※ Note Be sure to launch DAX Studio from the pbix file containing the data model.

• https://marshal115.hatenablog.com/entry/2021/12/24/000030#-BI導入の準備

Example of use: Measuring query performance Check the specs of your PC Check to see if your PC is fast enough to use Power BI DEFINE var _a = SELECTCOLUMNS(CALENDAR(1, 10000), “a”, [Date]) var _b = SELECTCOLUMNS(CALENDAR(1, 10000), “b”, [Date]) var _joined = CROSSJOIN( _a, _b ) var _result = COUNTROWS( _joined ) EVALUATE ROW("Result", _result ) ※ Tip ◆ Keep other applications down when testing ◆ Test with the PC connected to a power source

Example of use: Query Editor function Practicing DAX Learn how to write DAX with DAX Studio. ① Start with EVALUATE ② Write DAX ③ F5 to refresh Official site (English) https://daxstudio.org/tutorials/writing-dax-queries/

• https://daxstudio.org/tutorials/writing-dax-queries/

Example of use: Query Editor function Practicing DAX Easily check DAX queries in DAX Studio instead of Power BI 【Explanation of DAX Queries】 1. Add two columns for Product[Category]: [All Sales] and [FY2020 Sales] 2. Calculate [% of FY2020 Sales] (=[FY2020 Sales]÷[All Sales]) with the results of the two added columns ※All DAX queries built in DAX Studio are table-based results.

Example of use: Query Builder DAX beginner-friendly 【 Advantages of the Query Builder 】 ◆ Easy to build DAX queries even for beginners ◆ Can study automatically constructed queries ◆ Can export data in any form you like (* be sure to consider Power BI governance before exporting data) ◆ Can convert these queries to Excel (see blog below) Tech-tech blog（Japanese） Application of DAX Query

• https://marshal115.hatenablog.com/entry/2021/10/16/164259#おまけ

Example of use: Displaying metadata information Export / import of VPAX file 1. DAX Studio > Advanced > Export Metrics > Save 2. Launch an empty DAX Studio > Advanced > Import Metrics > Select .VPAX to import (see below) 3. Used to check models that do not contain original data (metadata only)

Exporting Data There is no export limit, but it depends on the specifications of the local PC. I’ve exported 600 million lines (6 GB) of data in success by Darren

Example of use: Data export function Batch export to CSV files

Example of use: Data export function Batch export to CSV files

Example of use: Data export function Batch output to Database Bulk output from DAX Studio to SQL servers with write permission

Example of use: Data export function Batch output to Database

Example of use: Data export function Batch output to Database

Example of use: Data export function Batch output to Database

Example of use: Data export function Batch output to Database

Example of use: Data export function Connect to the database Connecting with Power BI Desktop

Example of use: Data export function Connect to the database

Example of use: Data export function Extract M queries from DAX Studio (DMV) Connect to Power BI Desktop / Power BI Service and fill in the following code ※ Note 1. DMV = Dynamic Management View Queries that return information about model objects, server operations, and server health 2. For Power BI Service, Premium capacity or PPU required Reference: Chris Webb's blog

• https://blog.crossjoin.co.uk/2019/05/08/extract-m-code-dmv/

Example of use: Data export function Extract DAX queries from DAX Studio (DMV) SELECT MEASUREGROUP_NAME, MEASURE_NAME, EXPRESSION FROM $SYSTEM.MDSCHEMA_MEASURES WHERE MEASURE_AGGREGATOR = 0 ORDER BY MEASUREGROUP_NAME ※ Note 1. DMV = Dynamic Management View Queries that return information about model objects, server operations, and server health

Example of use: Other Obtaining the server name (Port) Launch DAX Studio with Power BI Desktop or other software. 2. Click on the icon in the lower right corner of the editor (see below) 1. 3. Launch Notepad, etc., and make sure that the name has been acquired 4. Put the above into Excel or SSMS servers to enable connection. Excel: “Data” tab > Get Data > From Database > From Analysis Services > Server Name

Example of use: Other Analyze in Excel feature “Analyze in Excel" functionality available from DAX Studio Tech-tech blog (Japanese) Analyze in Excel ① https://marshal115.hatenablog.com/entry/2021/06/13/171554 Analyze in Excel ② https://marshal115.hatenablog.com/entry/2021/10/16/164259

• https://marshal115.hatenablog.com/entry/2021/06/13/171554
• https://marshal115.hatenablog.com/entry/2021/10/16/164259

Main Resources DAX Studio official site (English) https://daxstudio.org/document ation/ Tech-tech blog (Japanese) https://marshal115.hatenablo g.com/ Others (Qitta -Japanese) https://bit.ly/3bGtDoK

• https://marshal115.hatenablog.com/
• https://daxstudio.org/documentation/
• https://bit.ly/3bGtDoK