A while ago I wrote a blog post about Azure SQL Data Warehouse and Power BI which I explained how to install a new instance of Azure SQL Data Warehouse and how to visualise your Azure SQL DW data in Power BI. In this post I explain how to query an Azure SQL DW in SSMS and Visual Studio.
Querying Azure SQL Data Warehouse from Visual Studio
Prior the latest release of SQL Server Management Studio (SSMS) 2016, the only available tool for querying an Azure SQL Data Warehouse was SQL Server Data Tools (SSDT) for Visual Studio 2013 or 2015. Here is how you can use SSDT 2015 to query an Azure SQL Data Warehouse:
Continue reading Query Azure SQL Data Warehouse in SSMS and SSDT
A while ago I wrote a blog post about Power BI Publisher for Excel. Today I want to explain some new features added to the publisher. In this post you learn how to analyse Power BI data in Excel. Using the new Power BI Publisher for Excel, not only can we pin an Excel range or chart to a Power BI dashboard directly from Excel, but also we are now able to easily connect to a Power BI service, select any group workspaces and analyse a desired report or dataset.
- Desktop versions of Microsoft Excel 2007 and later
- Download and install Power BI Publisher for Excel
- Power BI Publisher for Excel add-in will be enabled by default after you install it, however, if you don’t see the “Power BI” tab in the ribbon in Excel you can enable it from File –> Options –> Add-ins –> COM Add-ins –> tick Microsoft Publisher for Excel.
Connect to and Analyse Power BI Data in Excel
Analyse Power BI Service Reports or Datasets in Excel (From Power BI Service)
Previously we could analyse Power BI data in Excel directly from Power BI service by:
- Log in to Power BI Service
- Clicking ellipsis button of a desired dataset and clicking “Analyse in Excel”
- Clicking ellipsis button of a desired report and clicking “Analyse in Excel”
- Doing either way, it downloads an “odc” file that could be opened in Excel.
- Now you can analyse the data in Excel using pivot tables and pivot charts.
Continue reading Analyse Power BI Data in Excel
One of the most powerful features in Power BI and Excel is supporting geospatial visualisations. In Excel we can use Map visualisation in Power View, or use Power Map directly. In Power BI, as you know, there are two built-in visualisations supporting geographic coordinate data, Map and Filled map. They work beautifully if you have enough data supported by Bing Maps. But, there are some issues with Map visualisations in both Power BI and Excel. In this post I address some of the issues I faced myself and I’ll provide the solutions for the issues. As “Filled Map” and “Map” visualisations in Power BI are very similar my focus in this post would be on “Map” visualisation. My intention is not explaining Power View and Power Map that much so my focus in this article would be on Power BI more than the other two.
To experiment everything I explain in this post you need to have:
- The new SQL Server sample, WideWorldImportersDW (WWI). You can download it here
- The latest version on Power BI Desktop (current version is 2.35.4399.381 64-bit (May 2016))
- Excel 2016 or Excel 2013
If you use Excel 2016, then you need to turn on Power View on.
Check this out if you want to learn more about BI features in Excel 2016.
Get Data in Power BI
- Open Power BI Desktop
- Get Data from SQL Server Database
- Select Fact.Sales and Dimension.City then load data
Map Issues In Power BI
Wrong Cities in Power BI
- Expand the “Dimension City” table
- Select “City” column then change its Data Category to City (Data Category is on “Modeling” tab from the ribbon)
- Put a Map visual into the page
- Put “City” on Location
- Put “Total Excluding Tax” on Size
As you see sales distributed across different countries, but, this is not quiet right.
- Put a slicer on the page then put “Country” on the slicer
- Click “United States” to filter the Map
Oops! This is not quiet right. What happened is that Bing Map Engine gets confused with the city names so that it shows a city with the same name outside of the US, just like New Plymouth which a city in New Zealand, but, the New Plymouth we have in our data source is the New Plymouth from Idaho in the US.
Continue reading How to Overcome Map Related Issues in Power BI, Power View and Power Map
Note: This article has been updated on June 2017 to support latest versions of Power BI Desktop and SSAS Tabular 2017.
Have you created a robust model in Power BI Desktop and you are looking for a way to import it to an instance of SQL Server Analysis Services Tabular? Hmm, it would be highly beneficial if you could import Power BI model to SSAS Tabular and it potentially saves lots of development time and costs. The good news is that with SQL Server 2016 and SQL Server Data Tools for Visual Studio 2015 it is possible. In this post I show you how to import Power BI Desktop model to SSAS Tabular 2016. Unfortunately, you cannot do the job in any prior versions of SQL Server, SQL Server Management Studio or SSDT.
- SQL Server 2016 Tabular: You can download SQL Server 2016 Developer Edition for free. Check this out for more information
- SQL Server Management Studio (SSMS) 2016: Down SSMS 2016 from here
- SQL Server Data Tools for Visual Studio 2015 (SSDT 2015): You can download it here
- Power BI Desktop: Download Power BI Desktop from here
How it works?
The idea is to
- Connect to Power BI Desktop model from SSMS 2016
- Script the model
- Modify the script
- Execute the scripts on your on-premises instance of SSAS Tabular 2016
- Open the new SSAS Tabular database in SSDT 2016
- Modify the model
- Redeploy and process the model
Note: Do not close Power BI Desktop until we completely import the model to SSAS Tabular.
- You’re familiar with all required tools listed above
- You’re familiar with SQL Server Analysis Services Tabular models and any corresponding concepts, security settings and so forth
- You’re familiar with DAX and Power Query
Continue reading Import Power BI Desktop Model to SSAS Tabular 2016