Upcoming Power BI and Azure SSAS training in the UK, Israel and the Netherlands

If you have some training budget to spare why not come along to one of the pre-conference seminars/training courses I have coming up in the next few months? Specifically:

  • Introduction to Azure Analysis Services – a pre-conference seminar on Thursday 22nd February at SQLBits 2018 in London. You’ll learn about what Azure Analysis Services is, why you should use it and how to build Tabular models for it. More details and registration here.
  • Power BI for analysts and developers – a three-day course covering the whole Power BI development lifecycle in Herzliya, Israel, on March 12th-14th. Full agenda and registration here.
  • Loading and transforming data in Power BI and Power Query – a pre-conference seminar on Friday 23rd March before the Dutch Power BI User Day in Utrecht. It will cover everything you need to know about the Query Editor in Power BI and Excel, going from the basics to more advanced topics such as parameters, functions and the M language. Full agenda here.

Incidentally, if you’re in London for SQLBits don’t forget to come to the London Power BI User Group meeting on Wednesday 21st February: I’m doing a panel discussion with various other Power BI folks. I’m also going to be speaking at the Dublin Data and BI Summit (run by the Power BI User Group) at the end of April, and at several other user groups later in the year; follow me on Twitter (I’m now @cwebb_bi) to find out the details when they’re available. I hope to see you at one of these events!

Setting SQL Server Connection String Properties In Power BI and SSAS Tabular Modern Data Sources

It may not be immediately obvious, but you cannot set your own connection string properties when connecting to SQL Server using the built-in SQL Server connector from either Power BI or a modern data source in Azure SSAS/SSAS Tabular 2017:


All you can do is configure the options that are available in the UI, which in the current version of SSDT looks like this:


…and which are documented in the Sql.Databases() M function here.

It turns out that the restriction on using your own connection string properties in the built-in SQL Server connector is a deliberate design decision on the part of the Power Query team because, behind the scenes, they use different providers in different circumstances to optimise performance, and because allowing arbitrary connection string properties might make maintaining backwards compatibility difficult in the future.

While your average Power BI user is unlikely to even notice this, for SSAS Tabular developers it could be a big problem: complete control over the connection string is often necessary in enterprise BI scenarios. What are the alternatives then? Well you can use the OLE DB and ODBC connectors instead:


Both of these connectors do allow you to set your own connection string properties. For example here’s the UI for a new ODBC connection in SSDT:


The documentation for the Odbc.DataSource and OleDb.DataSource M functions has more detail on how these connectors can be used and how connection string properties can be set. Remember also that the OLE DB Provider for SQL Server was un-deprecated in October 2017.

However, apart from possible performance differences between the two (which you should test yourself – Henk van der Valk wrote a good post on this for SSAS MD and most of what he said is relevant for Tabular) there’s one less-than-obvious difference between these two options: the OLE DB connector does not appear to support query folding right now whereas the ODBC connector does. Of course this isn’t an issue if you’re writing your own SQL queries to import data, but if you do want to use M functions for partitioning (as I show here) you’re likely to get very poor performance with the OLE DB connector.

Using Your Own SQL Queries For Tables With Modern Data Sources In SSAS 2016+ And Azure Analysis Services

While the integration of the Power Query engine into Analysis Services Tabular 2017 and Azure Analysis Services with modern data sources will certainly bring a lot of benefits, I think it’s fair to say that the implementation has not been entirely painless. One problem is that it is no longer obvious how to specify your own SQL query to populate a table or partition in your Tabular model – and while the Query Editor is great, there are a lot of cases where this is necessary. In this post I’ll show you how to do this.

If you’re used to using the Power Query UI in Excel or Power BI Desktop, you’ll notice that when you connect to a SQL Server database using the SQL Server connector in SSDT:


…there is no option to enter your own SQL query when you do so:


This is deliberate. In Analysis Services, unlike Power BI and Excel, there is a distinction made between data sources and other M queries that return data from those data sources, one that makes a lot of sense in my opinion. While it is possible to enter your own SQL for other data source types, such as OLE DB connections, a data source object is really intended just to define a connection to a data source and not to define what data you want from that data source.

[You may also notice that there’s a “SQL statement” property on a SQL Server data source visible in the Visual Studio properties pane, but I don’t recommend you use it – it doesn’t seem to work well with the rest of the SSDT/Power Query UI]

To import a table or view in your database all you have to do is right-click on your data source and choose Import New Tables; my blog post from September last year describes how to do this, and how to use M functions for creating partitions.

To use your own SQL queries though you need to write some M code. First, import a table – any table, but preferably a small one – and get to the Query Editor UI. In this case I’ve imported the DimDate table from the Adventure Works DW database:


Next, select your query in the Queries pane on the left-hand side of the screen and open the Advanced Editor either by clicking on the relevant button in the toolbar (shown above) or by right-clicking on the query name in the Queries pane. You’ll see the following dialog:


The M code will be something like this:

    Source = #"SQL/localhost;Adventure Works DW",
    dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data]

In this example the Source step creates a reference to the data source you have already created, and the dbo_DimDate step gets the contents of the DimDate table from this data source.

You can modify this code to use your own SQL by using the Value.NativeQuery() function (which I have blogged about here):

    Source = #"SQL/localhost;Adventure Works DW",
    MyQuery = 
		#"SQL/localhost;Adventure Works DW",

Here what I’ve done is replaced the dbo_DimDate step in the previous query with a step called MyQuery that uses Value.NativeQuery() to run my own SQL.


Now all you need to do is click Import and you have the output of the query loaded into SSAS. It would be nice if there was UI support for using your own SQL queries when importing data in the future. Note that, as soon as you use this method, any other steps or queries further downstream will not be able to perform query folding, so you should make sure that you do as much of your filtering and transformation in the SQL as possible otherwise you may encounter performance problems.

The documentation describes a similar – but not identical – workflow for achieving the same result here. Personally I think it’s counter-intuitive that you should click on Expressions to create a Table object! Expressions are used for functions and other M code that is shared by the M queries used by Tables.

An alternative to doing all this is to go back to the old way of doing things and use a legacy data source rather a modern data source in SSDT. You lose the ability to use the Query Editor and M if you do this, but in a lot of cases you probably won’t care. The 17.4 release of SSDT for Visual Studio 2015, released in December 2017, has exposed a property that allows you to create legacy data sources again easily. In Visual Studio, go to the Tools menu and select Options and in the Options dialog go Analysis Services Tabular/Data Import and check “Enable legacy data sources”:


When you do this, you’ll notice two new options when you right-click on Data Sources in the Tabular Model Explorer pane: Import From Data Source (Legacy) and Existing Connections (Legacy).


This gives you access to the Table Import wizard that was available in previous versions of Analysis Services Tabular, which not only allows you to enter your own SQL but also creates a legacy data source that in turn makes it easy to use your own SQL when creating partitions.

In-Person Power BI And Azure Analysis Services Training In London

I’m pleased to announce two in-person training dates in London next year.

First of all, I’ll be running a three-day “Introduction to Power BI” course from January 29th to January 31st 2018.  Suitable for BI pros, analysts and anyone who needs to use Power BI to build reports, I’ll be covering data loading, data modelling, a bit of M, a lot of DAX, report design, publishing, security and administration. Full details and registration can be found here:


Not long after, on February 22nd 2018, I’ll be teaching a full-day preconference seminar at SQLBits in London on Azure Analysis Services. The agenda can be found here:


…but, to be honest, there’s likely to be a lot of cool new functionality released for Azure SSAS between now and then so that will all have to be fitted in too. SQLBits is, of course, the biggest SQL Server/Microsoft data platform conference in Europe, ridiculously good value for money and loads of fun. Pricing and registration details can be found here:


UPDATE: the SQLBits team wanted me to make a video to promote my precon, so I outsourced the job to my daughter. Here’s the result:

Creating A Partitioned Table In SSAS Tabular 2017 And SSDT Using M Functions

One of the coolest new features in SSAS Tabular 2017 and Azure Analysis Services is the integration of Power Query and M for data loading. Over the last year or so the Analysis Services team blog has posted a lot of fairly complex examples of how to use this functionality, but now that the latest release of SSDT has proper support for shared expressions I thought it would be a good idea to show a simple example of how to use it to create a partitioned table using M functions.

For this example I’ll be using the FactInternetSales fact table from the Adventure Works DW sample database, and the aim is to create a table in an SSAS Tabular project that has one partition for each year of data in FactInternetSales. Assuming that a new SSAS Tabular project has been created at the 1400 compatibility level with an integrated workspace:


…the first thing to do is to right-click on the Data Sources folder in the Tabular Model Explorer pane and select Import From Data Source:


This brings up the Get Data dialog:


Select SQL Server database and then click Connect. Enter the server name and database name in the SQL Server database dialog:


Choose how SSAS is to authenticate when it connects to the SQL Server database and click Connect:


Select the FactInternetSales table from the list of tables in the Adventure Works DW database:


This opens the Query Editor window; in it there is one query called FactInternetSales:


Here’s where it gets interesting. The first thing to do is to create a function that returns a filtered subset of the rows in the FactInternetSales table using the technique I blogged about here for Power BI. On the Query Editor menu bar, click Query/Parameters/New Parameter and create two new parameters called StartDate and EndDate that return the numbers 20010101 and 20011231. Here’s what they should look like:



These parameters are going to be used to filter the OrderDateKey column on the FactInternetSales table. Do this by clicking on the down arrow on the column header of OrderDateKey then selecting Number Filters and then Between:


In the Filter Rows dialog use the StartDate parameter for the start of the filter range and the EndDate parameter for the end of the filter range, then click OK:


Because the OrderDateKey contains dates stored as numbers in the YYYYMMDD format the result is a table that only contains sales where the order date is in the year 2001. This table should not be loaded into SSAS though, so right click on the FactInternetSales in the Queries pane and make sure that the Create New Table is not checked:


Next, on the same right-click menu, select Create Function:


In the Create Function dialog name the new function GetFactData then click OK:


The new GetFactData function will now be visible in the Queries pane; enter 20010101 for the StartDate parameter and 20011231 for the EndDate parameter and click Invoke:


This creates yet another new query called Invoked Function which should be renamed Internet Sales:


Right-click on this query and make sure Create New Table is selected. Next, click the Import button on the toolbar to close the Query Editor and load the Internet Sales table into SSAS.

At this point the Tabular Model Explorer will show all of the queries created above listed under the Expressions folder, and a single table called Internet Sales with a single partition:


Next, right-click on the Internet Sales table in the Tables folder and select Partitions:


This opens the Partition Manager dialog. Rename the existing partition to Internet Sales 2001:


Note that the M query for this partition calls the GetFactData() function to get the rows from FactInternetSales where OrderDateKey is between 20010101 and 20011231:

    Source = GetFactData(20010101, 20011231)

Click the New button to create new partitions, one for each year of data in the FactInternetSales table. Each new partition will initially contain the same M code shown above and should be edited so that the query gets data for the appropriate year:


Click OK, and the end result is a table with one partition per year:


What’s the point of using M functions to return the data for a partition, rather than the traditional method of using a SQL query embedded in each partition? One reason to do this would be to make maintenance easier: if you need to do something like add a new column to a fact table, rather than editing lots of partitions you just need to edit the function and all the partitions will reflect that change. I can think of a few others, but I’ll save them for future blog posts…

Deleting Cached Logins For Azure Analysis Services In SQL Server Management Studio

When Azure Analysis Services was announced I had to try it out right away. Of course I didn’t read the instructions properly so when I tried to log in to my Azure Analysis Services instance from SQL Server Management Studio, like an idiot I logged in with the wrong username. The problem is that once you’ve done this, with current versions of SQL Server Management Studio there’s no way of logging out and logging in as a different user. Luckily Igor Uzhviev of Microsoft had a solution for me and I thought I’d share it for anyone else who’s made the same mistake. Here’s what you need to do:

  • Go to your user local app data folder (the path is like this: C:\Users\<your_user_name>\AppData\Local\)
  • In this directory you need to delete:
    1) Any .dat files with names that start with "omlibs-tokens-cache…"
    2) The folder AADCacheOM, if it exists
  • You also need to open Internet Explorer and delete  all cookies and website data, because IE caches auth tokens too

Once you’ve done this, the next time you try to connect to Azure Analysis Services from SSMS you’ll get prompted to log in again. Future releases of SSMS will have proper support for logging in and out of Azure SSAS, but even with the latest version (version 16.5) you’ll still need to following these steps.

First Thoughts On Azure Analysis Services

Hurray! At last we have proper cloud-based Analysis Services! The official blog post with the announcement is here:


…the pricing details are here:


…and the documentation is here:


It’s still in preview, so while there are some missing features I’m confident that they’ll be added soon. Similarly it’s SSAS Tabular only right now, but the blog post says

Support for multidimensional models will be considered for a future release, based on customer demand.

I’m pretty sure there there will be plenty of demand for Multidimensional support given the installed base that’s out there.

Why should we be excited about this, though? What will Azure Analysis Services be useful for? Obviously, if you want to build a completely cloud-based Microsoft BI solution then Azure SSAS is an important component to have available. Also, consider the fact that the load on a typical SSAS server varies a lot over time: daytime is usually much busier than night-time, weekdays are usually busier than weekends, and some days of the month (such as month ends) may be much busier than others. It will be great to be able to build cloud-based SSAS solutions that can be scaled-up and scaled-down to meet demand, rather than expensive on-premises SSAS solutions that are underutilised most of the time. I also think Azure SSAS will be very interesting to ISVs, and to Power BI users looking to move up from native Power BI storage, although in both cases pricing will be key to adoption. I can’t wait to start using this with my customers!

UPDATE 27/10/2016: You can see the top-rated requests on the Azure Analysis Services forum here https://feedback.azure.com/forums/556165 unsurprisingly, support for SSAS Multidimensional is #1