In some of my old posts, which are most popular ones, I explained how to Export data Power BI Desktop or Power BI Service data to different destinations like CSV, Excel and SQL Server. In this quick tip I explain a very easy way to export the model data as a whole to either CSV or SQL Server with DAX Studio.
Daniil from XXL BI well explained this method, but I’d rather quickly explain how it works and add some more information.
After release 2.8 of DAX Studio, you can now quickly export the whole model to CSV and SQL Server in one shot.
XMLA endpoint connectivity for public preview has been announced late March 2019. As at today, it is only available for Power BI Premium capacity users. This sounds like a massive restriction to a lot of people who don’t have a Premium capacity, but they’d love to see how it works. In this article I show you an easy way to get your hands to Power BI XMLA endpoint as quick as possible. Before I start, I’d like to simply explain what XMLA endpoint is and what it really means for Power BI users.
Power BI is Like Onion! It has layers!
Generally speaking, Power BI has two different layers, presentation layer and data model layer. Presentation layer is the visual layer, the one you make all those compelling reports and visualisations. The data model as the name resembles, is the layer that you make your data model in. This layer is the one you can access it via XMLA connectivity.
In a Power BI Desktop file, you can see both layers:
Seriously? Yes, seriously. It is not new. It’s been around
for many years and perhaps you’ve already used it zillions of times. Whenever
you’re connecting to an instance of SQL Server Analysis Services, either
Multidimensional or Tabular from any tools like SQL Server Management Studio
(SSMS), Power BI Report Builder, Excel, Tableau, etc…, you’re using XMLA
Power BI is an Instance of SSAS Tabular
It is true. Power BI runs a local instance of SSAS Tabular
model. So, whenever you open a Power BI Desktop file (PBIX), Power BI creates a
local instance of SSAS Tabular model with a random local port number that can
be accessed on your local machine only. When you close the file, the local
instance of SSAS Tabular is shut down and its port number is released.
you are a Business Intelligence consultant working in Power Platform, Azure
Logic Apps and Azure Analysis Services landscape, you probably felt that
On-premises Data Gateway is one of the essential parts of your engagements with
the your customers. Installing On-premises Data Gateway can go smoothly if you
already have a well thought implementation plan otherwise, it can quickly turn
to a beast if you don’t have one. In this post I do my best to provide you some
guidelines that can help you with your On-premises Data Gateway implementation
planning. Consider the following points before, during and after the
Culture of Engagement
Environments (with all peopleinvolved)
Identity Access Management
Installation, Configuration and Testing
Here is a diagram of important point that you should consider:
need to understand the use of On-premises Data Gateway for your customer. If
they need the gateway for their Power Platform, Azure Logic Apps, Azure
Analysis Services or all of them. This is important as you either need to have
access to your customer’s Power BI Service or Azure Portal or both, or you need
to assist your customer to configure On-premises Data Gateway in Azure or in
Power BI Service. The next points are:
Accessing customer’s Azure Portal and/or Power BI Service: The customer to decide whether to create a new account with sufficient rights for you or give you the credentials of an existing account. It is important to make sure you can access all environments and you have necessary rights to install/configure the gateway
You assist/consult a person at customer side with the implementation: you need to make sure you communicate with that person and see if he/she understands the requirements before the implementation date. Send them a calendar invitation beforehand to make sure he/she is present at that date. Always ask for a backup person just in case of an emergency happening to the primary person.
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.
The latest version of Power BI Desktop (Current version is: 2.40.4554.463 64-bit (October 2016))
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.
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.
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:
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