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.
- 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”
- 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?
- 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”
Continue reading Import Power BI Desktop Model to SSAS Tabular 2017 Using Azure Analysis Services
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.
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.
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
I’d like to thank you all for attending the webinar held on 30th September 2016. I talked about some amazing under cover aspects of Power BI Desktop model. In this session you learnt:
If you’ve missed the webinar you can watch it online here:
Download the Power Point presentation file here.:
Download (PPTX, Unknown)
Here is the PDF version of presentation:
Download (PDF, Unknown)
You’ve changed something in your Tabular model and you’re facing an ugly “Error: Object reference not set to an instance of an object.” message, don’t rush. Just go to the table you’ve made some changes and click on “Partitions” button.
Then click “Refresh Preview” button and click OK.
SSAS 2012 supports three different approaches for creating a BISM (Business Intelligence Semantic Model):
a. Uses relational modelling constructs like such as tables and relationships
b. Uses xVelocity in-memory analytics engine for sorting and data calculations
c. Needs to use SSDT (SQL Server Data Tools) to implement
d. Can import data from relational data sources using OLE DB native and managed providers
e. Tabular solutions only support one model.bim file per solution, which means that all work must be done in a single file. Development teams that are accustomed to working with multiple projects in a single solution might need to revise how they work when building a shared tabular solution.
f. support DAX calculations, DAX queries, and MDX queries
g. Tabular model databases can use row-level security, using role-based permissions in Analysis Services (DAX implementation required)
h. It might be not a good choice for the systems that are going to load terabytes of data
Continue reading Three different approaches for creating a BISM in SSAS 2012, Tabular vs. Multidimensional vs. PowerPivot