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

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

image

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

Exporting Power BI Data to SQL Server

 

Exporting Power BI Data to SQL Server

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 myself, 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 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 details 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 existing Power BI Desktop model to SQL Server and I explain it step-by-step.

Requirements

To make this method work you need to:

  • 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 a “RODBC” folder. Then all you really need to do is to copy the “RODBC” to the “library” folder 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 “Internet Sales” model created on top of AdventureWorksDW2016CTP3. You can download my Power BI Desktop model at the end of this post.)

Continue reading Exporting Power BI Data to SQL Server

Exporting Data from Power BI Desktop to Excel and CSV – Part 2: Importing Power BI Data to Excel Directly

AAEAAQAAAAAAAAz4AAAAJGQ5ZTk4ZGUxLTI4YjQtNDc4ZC05NTQ4LWRjNDk3OTBlYTE5OQ

In my previous post I explained how to copy and paste data from Power BI Desktop into Excel or CSV. I also explained how easy you can export Power BI Desktop data to CSV using DAX Studio. As I promised, in this post I show you how to import Power BI Desktop data to Excel directly. In this method you don’t need to use any third-party software and the performance is much better than the previous methods.

Note: The method I explain in this post is tested in Excel 2016 only. But, it should work for Excel 2013.

Importing Power BI Desktop Directly to Excel Directly

In one of my previous posts I explained how to connect to a Power BI Desktop from Excel. To import Power BI Desktop data to Excel we have to do the same thing. I explain the way to connect to a Power BI Desktop model directly from Excel again then I show you how to use this method to import Power BI Desktop data.

Finding Power BI Desktop local port number from Power BI Desktop temp directory

We can find Power BI Desktop local port number in number of ways explained here. So in this post I don’t go through all methods.

Whenever we run Power BI Desktop, it opens a random port number. The port number is independent of the model so it doesn’t really matter if  we haven’t connected to any data sources or even if we haven’t open any saved Power BI Desktop (*.PBIX) files. That port number is stored in a text file named “msmdsrv.port.txt”. So the only thing we need is to do is to browse the temp directory of Power BI Desktop and open the “msmdsrv.port.txt” text file. You can find Power BI Desktop temp folder here:

%LocalAppData%\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces

There should be an “AnalysisServicesWorkspaceXXX” folder which XXX is a random number. Open that folder then open “Data” and Find “msmdsrv.port.txt”. Open the file to see Power BI Desktop local port number.

image

Continue reading Exporting Data from Power BI Desktop to Excel and CSV – Part 2: Importing Power BI Data to Excel Directly

Exporting Data from Power BI Desktop to Excel and CSV – Part 1: Copy & Paste and DAX Studio Methods

AAEAAQAAAAAAAAz4AAAAJGQ5ZTk4ZGUxLTI4YjQtNDc4ZC05NTQ4LWRjNDk3OTBlYTE5OQ

One of the features that is asked a lot on Power BI community is how to export Power BI desktop data to Excel OR CSV.

Note: I’d like to make it clear that in this article we are NOT interested in exporting data from a visualisation in a report to CSV.

The first question lots of you might have is “How on earth someone wants to export data from a Power BI Desktop model to Excel OR CSV?”.

Power BI brings Power Query, Power Pivot, Power View and Power Map together in one piece of software. So why not using Excel at the first place to load data from the source? There might be lots of other questions about the reasons that someone wants to export data from Power BI Desktop model to Excel (or any other destinations). The reason could be one of the following that someone wants to export data from Power BI Desktop model to other destinations like Excel, CSV, SQL Server etc.

  • For some reason you have just a Power BI Desktop file (PBIX) and you don’t have access to the data sources and you need to provide the data to someone who is not familiar with Power BI
  • You Power BI Desktop consolidates lots of different sources in a single model and it would be very hard to get the same output as you get in Power BI Desktop model in Excel. So an export feature can be super handy
  • You might have done lots of complex transformations in Power BI Query Editor and replication the same logic on the source system could be much more complex and time consuming, so again exporting data from a current Power BI Desktop model makes sense
  • You have a bunch of calculated columns created in DAX and you don’t want to go back and redo all the hard works you have already done in Power BI in another environment like Excel
  • You might want to use the current Power BI data in Cortana Analytics
  • You are just curious to see if it is possible
  • None of the above!

But, the reality is that regardless of the reason, lots of people still want to export data from Power BI Desktop to different destinations. So let’s have a look at different workarounds until this feature is not available in Power BI. I’ll explain different ways to export Power BI Desktop data in a series of articles. In this post you learn how to copy Power BI Desktop data to a destination file like Excel or CSV without any third-party software involved. I also explain how easy you can export Power BI Desktop data to CSV using DAX Studio.

Copy Data from Data View in Power BI Desktop and Paste it to Destination

The easiest workaround is simply copy/paste data from Data view in Power BI Desktop.

  • Open your Power BI Desktop model
  • Switch to Data view by clicking on Data tab

2017-03-02 15_12_09-AW, Internet Sales - Power BI Desktop.png

You have now 3 options to copy data:

Continue reading Exporting Data from Power BI Desktop to Excel and CSV – Part 1: Copy & Paste and DAX Studio Methods

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

Business Intelligence and Data Visualisation Articles