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” →

Power BI, Power BI Desktop

Visualise SQLite Data in Power BI

SQLite + Power BI

As the name implies, SQLite is a light weight transactional SQL database engine. It is open-source and free for use either for personal commercial. SQLite is widely used in mobile apps and many other desktop applications that need an internal light weight free SQL database. In this post I explain how to visualise SQLite data in Power BI.

Requirements

To be able to go through the process you need to meet the following requirements:

  • Latest Version of Power BI Desktop (Current version: 2.52.4921.682 64-bit (November/2017))
  • Install SQLite ODBC driver: Make sure you install 64 bit version if your Power BI Desktop is 64 bit version like mine!

Note: You may install both x32 and x64 bit versions of the driver if your other applications are in x32 bit.

  • An existing SQLite database

Note: In case you just want to try this and you don’t currently have a SQLite database you can download a SQLite version of “Internet Sales” of AdventrueWorksDW2016 that I made available for you.

How it works

Like any other Power BI cases, it’s really easy to load data from an existing SQLite database to a Power BI Desktop model. You just need to use ODBC data connection and connect to a predefined “ODBC DNS” OR use a “Connection String”. I’ll explain both methods. After you load data to your Power BI Desktop, you create the relationships then you’re good to go and create flashy reports. Continue reading if you’re interested to an step-by-step guide to visualise SQLite data In Power BI.

Importing SQLite Data to Power BI Using ODBC DSN

  • Open the correct version (x32, x64) of ODBC after you downloaded and installed SQLite ODBC Driver
  • Click “Drivers” tab and make sure SQLite ODBC Driver(s) successfully  installed

ODBC Drivers Continue reading “Visualise SQLite Data in Power BI” →

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” →

Power BI, Power BI Desktop, SQL Server, SQL Server 2016, SQL Server Analysis Services - SSAS, SQL Server Integration Services - SSIS, T-SQL, Uncategorized

Exporting Power BI Data to SQL Server

Exporting Power BI Data to SQL Server

Update 2021 March:

You can now export the data directly from Power BI Desktop using our free external tool, Power BI Exporter. Read more here.

Update 2019 April:

If you want to export the data model from either Power BI Desktop or Power BI Service to CSV or SQL Server check this out.

In the previous blog posts, I explained how to export Power BI data to Excel and CSV here and here. As promised in this post I explain how to export data from Power BI Desktop to SQL Server.

Hans Peter Pfister has already explained how to export data from Power BI Desktop to SQL Server using R scripts. Although Hans has done a brilliant job, it might be a bit hard to make it work if you don’t have any R experience and you don’t even know how to install and call R libraries. That’s so true about me, I’m NOT an R guy, but, who knows, maybe I will be. Smile 

But, what if you don’t want to go with R? If you are more involved with BI than analytics, then using R might not really be your cup of tea. Luckily, there is another way to export your Power BI data to SQL Server which is more BI friendly. You can export Power BI data to SQL Server using SSIS (SQL Server Integration Services). So if you are familiar with SSIS, then it might be your preferred choice.

With respect to Hans, in this post, I explain his method of exporting data from Power BI Desktop to SQL Server more in detail so that anyone who is not that familiar with R can make it work. I also explain how to export data from Power BI Desktop to SQL Server using SSIS. If there is any other methods you’re aware of please let me know in the comment section below.

Exporting Data from Power BI Desktop to SQL Server with R

As stated before, Hans has already explained this method here. So I don’t explain exactly what he did, but, I use his method to export data from the existing Power BI Desktop model to SQL Server and I explain it step-by-step.

Requirements

To make this method work you need to:

  • The latest version of Power BI Desktop, you can download it from here
  • Have access to an instance of SQL Server, either on your own machine or on a server in your local network to export the data to
  • Either install R for Windows, you can download it from here OR using an existing R-Server OR install SQL Server 2016 R Services
  • Install RODBC library for R, you can download the library from here

Note: I haven’t installed R Studio and nothing went wrong.

Installing RODBC Library for R and SQL Server R Services

As mentioned earlier, you can install R OR SQL Server R Services OR R-Server, but, as I haven’t tried R-Server myself I just explain how to install RODBC in R and SQL Server R Services.

You have to download the library from the link provided above, then extract the contents of the zip file which contains an “RODBC” folder. Then all you really need to do is to copy the “RODBC” to the “library” folder that exists in either R or SQL Server 2016 folders in your “Program Files” folder.

Library folder in R

Library folder in SQL Server 2016

How Does It Work?

Open an existing Power BI Desktop model that you’re willing to export its data to a SQL Server table and follow the steps below: (I use the “Internet Sales” model created on top of AdventureWorksDW. You can download my Power BI Desktop model at the end of this post.)

Continue reading “Exporting Power BI Data to SQL Server” →