Tag: SSAS Tabular

BISM Tabular, DAX, Power BI, Power BI Desktop, Power Query, SQL Server Analysis Services - SSAS, SQLite

SSAS Tabular Model Documenter with Power BI without DMVs

SSAS Tabular Documenter with Power BITechnology is growing fast and we are enjoying it. We reshape our daily created data in a form that satisfies our needs. One of the technologies which is used more commonly these days is SQL Server Tabular Models, SSAS Tabular in short. Lots of industries decide to go with SSAS Tabular in their new projects and some defined new projects to slowly switch their existing SSAS Multidimensional to SSAS Tabular. I know, there is a big debate around SSAS Multidimensional vs. SSAS Tabular. But my aim is to prevent going through that sort of discussion. As the title implies, this post is about documentation that I believe is one of the most important parts of every project which is also sacrificed the most. In this post I explain how to document your SSAS Tabular model in Power BI Desktop and Excel. I know, there are some products you can find in the internet that can generate documentation in various formats like Word, PDF, HTML and so on. But, If you’re looking for a free and somehow more intuitive way of documenting your SSAS Tabular Models with Power BI then this article is for you.  Through this article, we create a documentation tool with Power BI. I call it SSAS Tabular Model Documenter. In this method we don’t use DMVs at all. For those who are not familiar with DMVs I shortly explain what DMVs are, if you’re already familiar with DMVs you can jump this section.

You can download a copy of SSAS Tabular Model Documenter in Power BI template format (pbit) at the end of this post. It is a Christmas present for you.

What are DMVs?

Dynamic Management Views, DMVs in short, are queries that retrieve metadata information about an instance of SQL Server Analysis Services. DMVs work on both SSAS Multidimensional and SSAS Tabular server modes. DMVs can be used to monitor server operations and health. The DMV query structure is very similar to T-SQL, therefore you use “SELECT” statement followed by “$System” which is an XMLA schema rowset. The DMV queries look like below:

SELECT * FROM $System.<schemaRowset>

So you can open SQL Server Management Studio, connect to an instance of SSAS (Tabular model for the sake of this post) and run the following query to get lots of information about tables in your Tabular model:

select * from $SYSTEM.TMSCHEMA_TABLES

Running DMVs in SSMSRead more about DMVs here.

Note:This method only works with SSAS Tabular 2016 and above.

Document SSAS Tabular without DMVs

In SSAS Tabular 2016 and above there is a tiny metadata database that can be loaded in Power BI Desktop or Excel to document the corresponding SSAS Tabular model. The database is a SQLite database. In the previous post I explained how to visualise SQLite data in Power BI. The requirements for this post are the same as the previous post, so I encourage you to check it out. Therefore, I just explain how to find the metadata file and how to build a model in Power BI Desktop. I also explain how to do the same in Excel for those of you who would like to add some annotations or comments to the outcomes.

Continue reading “SSAS Tabular Model Documenter with Power BI without DMVs” →

Azure Analysis Services, BISM Tabular, Power BI, Power BI Desktop, SQL Server Analysis Services - SSAS, Uncategorized

Import Power BI Desktop Model to SSAS Tabular 2017 Using Azure Analysis Services

image

NOTE: This method is no longer available (from 1st March 2019) as Microsoft discontinued supporting Web Designer in Azure portal. Microsoft is working on an alternative solution, but there is no timeframes or details to announce yet. Read more here. In the meantime, you still can import your Power BI Desktop Models to SSAS Tabular following the method described here. 

A while ago I wrote a blog post on how to import you currently existing Power BI Desktop models to SSAS Tabular 2016. However, the method I explained is NOT supported by official Microsoft BI tools like SSDT, so you may consider it as a WORKAROUND only until Microsoft supports imploring Power BI models in SSDT. In this post, I show you how to import Power BI Desktop Model to SSAS Tabular 2017 using Azure Analysis Services. It is easy and hassle free.

Requirements

Notes:

  • In this post I do NOT explain how to install Azure Analysis Services
  • This method works only for SQL Server Analysis Services 2017 Tabular

How it works

As mentioned earlier it is really easy in compare with other methods I explained in my previous post. Azure Analysis Services is capable of importing Power BI Desktop files creating a Tabular model version of your Power BI model in the cloud. Then you can simply download Visual Studio project file and redeploy it in your on-premises instance of SSAS Tabular 2017. Let’s go through the steps…

  • Open Power BI Desktop
  • Import data from WorldWideImportersDW from any desired combination of fact tables and dimensions. I imported
  • Create some simple Measures like:

Total Sales = SUMX(‘Fact Sale’, ‘Fact Sale'[Unit Price] * ‘Fact Sale'[Quantity])

  • Save your Power BI Model and close the file
  • Login to your Azure PortalBrowse to your instance of Azure Analysis Services
  • Click on “Open” under “Web designer—preview”

image

  • This opens a new page for fabulous “Azure Analysis Services Web Designer”. You’re right, a web designer for tabular models. How cool is that? Smile
  • Click “Add” button under “Models” section
  • Yes, you got it, enter a name for your model and click “Power BI Desktop” button
  • Click “Browse” and select the Power BI file you saved earlier then click “Import”

image

Continue reading “Import Power BI Desktop Model to SSAS Tabular 2017 Using Azure Analysis Services” →

BISM Tabular, Business Intelligence - BI, DAX, Power BI, Power BI Desktop, Power Pivot, Power Query, Self-Service BI, SQL Server 2016

Creating Custom Table in SSAS Tabular using Table and Row Constructors in DAX

Dynamic_Measures_in_Card_Visual_-_Power_BI_Desktop

A while ago I was working on a Power BI project which the customer wanted to define a new table directly in the model. The easiest way to achieve this in Power BI Desktop is to “Enter Data” which creates a new table by typing or pasting in new contents. I was thinking of that project the other day and thought, hey, how we can do the same in SSAS Tabular when there is no Power Query (M) language available in SSAS Tabular 2016. The good news is that Power Query will be available in the next version of SSAS Tabular in SQL Server vNext. But, until then a workaround would be entering data to a CSV file then load it to the model. Another way is to implement custom tables in DAX in SQL Server 2016 Tabular models using Table and Row Constructors. In this post I show you a way of creating custom table in SSAS Tabular using table constructors in DAX. You can do the same in Power BI as the same principle applies. Therefore, in case you’d prefer not to use “Enter Data” feature which effectively uses Power Query to create a new table in Power BI Desktop, then you can use DAX to do the same.

Requirements

If don’t already have SQL Server 2016 it’s probably time to download and install it. I use AdventureWorksDW as sample database in this article.

Scenario

You are involved with an SSAS Tabular project and the customer asked for a report in Power BI with dynamic Card so that the values shown in the Card visual should dynamically change based on selected measure from a slicer. You have several different measures in the model and the customer wants to show some of them dynamically in only one Card visual. Consider you have the following measures to be shown in the Card:

  • Total Internet Sales
  • Internet Sales in 2014
  • Total Number of Internet Sales Transactions

You have to create a logic so that the users can selected any of the above measures to show in a single Card visual.

How it works

After you meet the requirements, you’re good to start implementing the above scenario in SQL Server Data Tool (SSDT). Creating a calculated table in SSAS Tabular 2016 is fairly easy. All we need to do is to create a custom table with two columns. One column stores friendly names for measures and the other one holds DAX expressions for the measures. As you might have noticed, I’m talking about creating a custom table in DAX and populating it with values. Continue reading to see how. What we are going to do is to create a calculated table using table constructors in DAX. Table and Row Constructors weren’t available in previous versions of DAX in SSAS Tabular. They are very similar to Lists or a list of Tuples just like what we have in MDX.

I’ll explain this later when we created our sample model in SSDT.

Continue reading “Creating Custom Table in SSAS Tabular using Table and Row Constructors in DAX” →
DAX, Power BI, Power BI Desktop

Using “IN” Operator in DAX

IN operator in DAX

If you are a SQL guy I bet you’ve used “IN” operator zillions of times. You might also looked for the same functionality in DAX and I’m sure you’ve found fantastic blog posts showing you how to mimic the same functionality in DAX. The October release of Power BI Desktop is full of new analytics features such as Grouping, Binning and TOPN filtering. On top of that, one new awesome feature that is not documented at time of writing this article, or at least I haven’t find anything over the internet, is “IN” operator in DAX. In this post I show you how to use it in your DAX expressions.

Requirements

Note 1: You need to install SSMS2016 to be able to write DAX queries provided in this article. Alternatively, you can use DAX Studio . If for any reasons you cannot use SSMS 2016 or DAX Studio and you only have Power BI Desktop, don’t worry, I’ll provide some examples in Power BI Desktop as well.

Note 2: If you run previous versions of SQL Server it’s absolutely alright. There is nothing special in AdventureWorksDW2016CTP3 for this article that you don’t get in older versions of the sample database. But, keep in mind that SQL Server 2016 Developer Edition is now free and you can download it very easily. Check this out if you’re interested to see how.

Getting Started

After downloading the latest version of Power BI Desktop run it then

  • “Get Data” from SQL Server
  • From AdventureWorksDW2016CTP3 load “FactResellerSales”, “DimProduct”, “DimProductCategory”, “DimProductSubCategory” and “DimDate” to Power BI Desktop model
  • Find the local port of Power BI Desktop by opening “msmdsrv.port.txt” file from the following path:

“%UserProfile%\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServicesWorkspaceXXXXXXXX\Data”

Note: The “XXXXXXXX” postfix is a random number. 

  • Open SSMS 2016 and connect to Power BI Desktop model as an Analysis Services local server. Do you want to learn more about how to connect your Power BI Desktop model from different software? Then check this out.

SSMS Connect to Power BI Desktop Model

  • Open an MDX new query
  • Run the following DAX query
EVALUATE
    SUMMARIZE('FactResellerSales'
                , DimDate[CalendarYear]
                , "Total Reseller Sales"
                , SUM('FactResellerSales'[SalesAmount])
                )

Here is the results:

Writing DAX in SSMS

Now we want to filter “CalendarYear” so that the query shows sales values for 2011 and 2012 only. One common scenario we had to do in prior versions of Power BI Desktop, Power Pivot or SSAS Tabular model was to use a logical OR operator “||” like below:

EVALUATE
FILTER(SUMMARIZE(FactResellerSales
                    , DimDate[CalendarYear]
                    , "Total Reseller Sales"
                    , sum(FactResellerSales[SalesAmount])
                    ) , DimDate[CalendarYear] = 2011 || DimDate[CalendarYear] = 2012
                    )

From now on we can write the above query using “IN” operator in DAX like below:

EVALUATE
    FILTER(
        SUMMARIZE(FactResellerSales
                    , DimDate[CalendarYear]
                    , "Total Reseller Sales"
                    , sum(FactResellerSales[SalesAmount])
                    ) 
            , DimDate[CalendarYear] 
                IN (2011, 2012)
            )

Here is the results:

IN operator in DAX

Continue reading “Using “IN” Operator in DAX” →