When you have a very large fact table in Analysis Services Tabular, you most likely have it partitioned. Suppose you are making some changes to the underlying source of the table and you hit “refresh table metadata” to add some new columns for example.

What happens behind the scenes is that Tabular Editor will run the query from the first partition against the source. If this is SQL Server, it will surround the query with SET FMTONLY ON to return only metadata. However, if for whatever reason this query takes a long time, you might encounter a timeout. This throws the error “Unable to validate source query”.

Not good. A quick fix is to go to your first partition, edit the query and just add WHERE 1 = 0 to the end of the query. (Note that I’m using the legacy sources, meaning I’m not using Power Query to extract data. But you should be able to add such a filter there as well.)

Now the table metadata should refresh instantaneously. I’ve logged a bug for Tabular Editor here. Just don’t forget to remove your filter from the partition when you’re done

So today I tried again with the latest version of SSDT. On the first attempt, the installer crashed again with the error message “the requested metafile operation is not supported”. I contacted the SSDT team again and they told me to reboot my machine and try again (no seriously, they did).

Anyway, when I launched the installer again after the reboot, it seemed the stand-alone SSDT shell was already installed and I just had to add SSIS/SSRS/SSAS. The installer gave same weird error about needing to close devenv (while nothing was open at the time), but I ignored the error and continued with the set-up. And lo and behold, SSDT 2017 was installed just fine. So sometimes a reboot really does work.

When opening VS 2017 for the first time, I was greeted with the following strange messages:

And team explorer:

And when I tried to close everything:

Weird stuff. But a reboot of Visual Studio fixed it again (yes, there’s a theme!). Everything seems to work fine and even the source control integration works, so I’m pleased to announce it’s safe to use SSDT 2017 (at least, when you have rebooted

There are many ways to avoid this scenario. The first one is to not deploy from Visual Studio at all, but rather use a script or the wizard. But you know those rogue BI developers, there will always be one who just right-clicks the Tabular project in Visual Studio and chooses “Deploy”. You could also use configurations in Visual Studio, which were introduced in Tabular 2016. You can find more info in this article. But of course you can still forget to pick the correct configuration before you deploy.

So I created a little script that can be executed in a SSIS script task. You execute it right before you start processing your models in your daily/nightly/hourly/weekly/… batch run. It will loop over the databases in your Tabular server using the Tabular Object Model (TOM), inspect the connections and make sure they are all set to point to the correct server. In the script, I assume SSAS is installed on the same server as the data sources. If this isn’t the case, it’s pretty easy to change. The server name is passed along as a SSIS project variable.

To make the script work, you need to install the correct libraries. More info can be found here. The script itself can be downloaded from Github.

When you’re working with a team on a Analysis Services Tabular project, you most likely will check your code into some sort of source control (yes, you do. If not, start tomorrow). One of the problems is the layout of the measure grid. This is stored in the model.bim.layout file and of course this isn’t kept in source control by default. This means that when I create new measures and organize them neatly in the grid and check in my changes, the layout file isn’t updated on the server (because it’s not included). Every developer has its own layout file. Which means that when another dev opens the model, the nicely organized measure grid is gone; all measures are typically splashed across two rows.

Annoying.

You can “solve” this by adding the .bim.layout file to source control (right-click the project, choose Add and then Existing Item).

Every time you work on the model, you check out the .bim.layout file as well. When you’re done, you check in all changes. When another dev starts working on the project, a get latest is performed to get the latest version of the layout file (typically you get a conflict error and you have to tell TFS to overwrite the local file). Case closed? Now we introduced a new issue: every time you want to check out the layout file, Visual Studio insists on opening it first. And that can take quite some time. Luckily you can disable this (hence the title of this blog post).

Go to Tools > Options… > Environment > Tabs and Windows. Deselect “Allow new files to be opened in the preview tab”. All done.

I’ve blogged a while back on how SSIS 2017 was finally here. Not much has changed; there is still no Visual Studio extension for SSIS, so if you want to create SSIS projects you still need to install SQL Server Data Tools (which makes it a bit useless that there are SSRS and SSAS extensions).

At my current client we are migrating to SQL Server 2017, so happy as I am I downloaded SSDT for Visual Studio 2017 (currently in the 15.4.0 preview version). I was happy because

• I could use Visual Studio 2017
• I didn’t need a full-blown Visual Studio, since there is a TFS Source Control Explorer for Visual Studio 2017 (VS 2015 doesn’t have this)

So I installed SSDT and the TFS plug-in. I configured the connection to the TFS server and the mapping and downloaded all our project objects. Then I tried to open an SSIS project. I was greeted with the cheerfully “The source control provider associated with this solution could not be found. The projects will be treated as not under source control.
Do you want to permanently remove the source control bindings from this project?“. Euh, what?

Turns out, the latest version of SSDT for VS 2017 breaks the TFS mappings. Happy days. Here’s a whole thread of people with the same issue. Also for giggles, check out the comments at the download page of SSDT. Of course there’s no older version available for VS 2017, only for VS 2015 or 2013. Funny, that download page says:

“The last few downloads are provided for the unlikely event that you experience issues with the latest release.”

Unlikely event?

So I removed SSDT and the TFS plug-in. I installed the full-blown Visual Studio (not happy, but you can choose which parts to install so you can go for a very light-weight install. Still not free like SSDT) and then I installed the SSDT 15.4.0 preview again. Finally, I removed the SSRS extension (version 1.17) and installed the last version (1.19). This time, the TFS integration seems to work fine. Another option would be to downgrade to Visual Studio 2015, but then again, there you also need the full Visual Studio for TFS integration.

On a sidenote: every time you close a solution in VS 2017, the annoying start page pops up. There’s a thread on StackOverflow on how to disable this (hint: it involves the registry).

Moral of the story: even Microsoft doesn’t know how to do integration testing.

“Invalid connection information to establish the server connection”

I was using SSMS 17.3 to connect to the instance, so I wasn’t doing anything wonky with connection strings. I tried the following, without any success:

• repair the SSAS instance (when does a repair ever work?)
• uninstall and re-install the SSAS instance
• uninstalling SSMS 17.3 and installing a slightly older version (17.0)

Nothing worked. I got a similar error when I tried to create a new project in Visual Studio using an integrated workspace. However, I could create an SSAS project using a local workspace server. I could create a model and deploy it to the server. And I could connect with Power BI Desktop to the SSAS instance. The plot thickens. Turns out I’m not the only one either who ever encountered this error:

Can’t connect to SSAS tabular nor MD on clean SQL Server 2017 Dev edition (on clean Win10 VM, SSMS V17.3)
Anybody else?#sqlhelp #SQL2017 pic.twitter.com/oT2GPfqhBv

— Nico Jacobs (@SqlWaldorf) October 11, 2017

I took a quick look at Event Viewer, and there were a bunch of errors in there as well for the OLAP service. Of course with pretty error messages:

Googling for this kind of messages led to one forum thread where someone had locale issues. Note the 8192 number, which isn’t even featured in the list of locale identifiers. Anyway, I fired up SQL Server Profiler and ran a trace while I was trying to log into the SSAS server. An error popped up:

Device attached to the system not functioning

This really didn’t make any sense. However, in one of the Discover Begin/End events, the same number appeared again: 8192 (this time explicitly marked as locale identifier). Hmmm, I had problems with weird locales before. I dug into my system, and yes, the English (Belgium) locale was lingering around. I removed it from my system and lo and behold, I could log into SSAS with SSMS again. Morale of the story: if you get weird errors, make sure you have a normal locale on your machine because apparently the SQL Server client tools go bonkers.

(note: there are some references to the en-be locale: here, here and here)

Errors in the high-level relational engine. The following exception occurred while an operation was being performed on a data source view: Method not found: ‘System.Threading.Tasks.Task`

A quick search indicated other people have struggled with this issue, but no solution were provided in the forums. After digging a bit more, I found related issues but rather for .NET developers, so it’s not for Tabular only. The solution was actually quite easy: upgrade the server to the latest .NET environment. After rebooting the server, I could finally process my Tabular model.

There have already been many posts/articles/books written about the subject of how CALCULATE and FILTER works, so I’m not going to repeat all that information here. Noteworthy resources (by “the Italians” of course):

• Filter Arguments in CALCULATE
• How CALCULATE works in DAX
• From SQL to DAX: Filtering Data

In this blog post I’d rather discuss a performance issue I had to tackle at a client. There were quite a lot of measures of the following format:

CALCULATE(measureX,FILTER(tableY,columnZ = "expression"))

We can create a very similar measure using the WideWorldImporters data warehouse:

It simply calculates the number of orders placed in the SouthEast Sales Territory:

Now, the problem with this formula is that it uses FILTER, which means an in-memory table is constructed to evaluate the rows and calculate the output of the filter. For every row. Don’t get me wrong, FILTER is very flexible and powerful, but because of its iterator behavior it can cause problems for certain models, as here was the case. The performance issue is also described in a blog post by Rob Collie: Quick Tip: Don’t Over-Use FILTER().

The problem is easily fixed (as outlined in Rob’s post): just remove FILTER(), because DAX will re-add it implicitly but now it’s optimized.

However, this has a small side effect:

When you include Sales Territory on the axis, the measure without filter repeats the same value for all values. This is correct though, as the ‘City'[Sales Territory] = “SouthEast” filter in CALCULATE overrides the current filter context of Sales Territory. The original measure however keeps the original filter context when it calculates the temporary results table of the FILTER function. This is why we get NULL values for all values except for the Southeast Sales Territory. Guess which one looks correct for the typical end user? The original measure of course. Which has performance issues.

My first instinct was to include some IF clauses to get rid of all the superfluous values of the second measure:

IF(HASONEVALUE(City[Sales Territory])
	,IF(VALUES(City[Sales Territory]) = "SouthEast"
		,COUNT('Order'[WWI Order ID])
		,BLANK()		
		)
	,CALCULATE(COUNT('Order'[WWI Order ID]), City[Sales Territory]="SouthEast" )
	)

The formula works, but looks a bit clunky. Luckily I got some assistance on Twitter when I discussed the issue with Marco Russo:

Use the second one. Wrap with keepfilters if you want to obtain the same result of the first one

— Marco Russo (@marcorus) September 5, 2017

With the KEEPFILTERS function, we can rewrite the measure as follows:

KEEPFILTERS will keep the original filter context, which means if Sales Territory is on the axis, it will actually filter the data:

The good news is of course the measure with KEEPFILTERS doesn’t suffer the same performance problem as the original measure, but it produces the same results. If you’re interested in all the nitty-gritty specifics, you can read chapter 5 and 10 of the excellent The Definitive Guide to DAX book.

I was almost 100% certain I added myself as an administrator during SQL Server set-up and the configuration of the Tabular server. I logged in the server, went to properties and then to security. The list of server administrators was as empty as a merchant’s soul (yep, I googled that one). I either forgot to add myself as an admin (not likely, because according to the docs the set-up doesn’t continue if you don’t select an admin), or something fishy was going on.

So I tried to add myself as an administrator, however no dice: I didn’t have the correct permissions. A quick search engine search (sorry) revealed I should be able to, because local administrators do have admin permissions in SSAS Tabular. This was starting to get annoying.

However, a bit more thorough search showed me the solution: you had to launch SSMS as an administrator. Good old UAC, you strike again, old foe.

And lo and behold, I could add myself as an admin! Which means I don’t have to run SSDT as an admin every time I want to create a new Tabular project.

I stumbled across a small MDX performance tuning trick when using SSRS reports (or any other client tool where you can edit the MDX). When you create the MDX query using the designer, it will append some cell properties at the end of the query. Basically it’s just metadata about the cells of the result set.

An example of what those values can contain:

• Value: 6360
• Formatted value: € 6.360,00
• Format string: \€ #,##0.00;\€ -#,##0.00

Some of them are quite useful, especially the FORMAT_STRING property as you can use it to force SSRS to display the formatting you configured in the cube. Be sure to check Jason Thomas’ excellent blog post on this: Using SSAS formatting in SSRS. FORMATTED_VALUE seems very useful, but since it’s a string value SSRS doesn’t know how to aggregate it, so using it in a tablix might result in an error.

However, most likely you won’t need most of those cell properties. So you can leave out the cell properties that you don’t need and it saves you some data that has to be retrieved from the cube and sent to the SSRS report. Don’t expect a 10-minute query to suddenly run under 5 seconds, but every little bit counts to make your reports as fast as possible.

I tested a simple query using MDX Studio, where you can easily wipe the cache of the cube.

It also has a nice Perfmon pane showing you some performance metrics about the executed query.

Here’s the query on a cold cache, with all cell properties:

On a cold cache with only two cell properties:

Warm cache with all properties:

Warm cache with two properties:

On a cold cache we got about 14.9% performance increase. On a warm cache about 16.5% increase. Quite a nice result for just deleting a couple of lines at the end of the MDX query. The more measures you include in your query, the bigger the impact that this trick has. If you only have one single measure, you probably won’t notice much difference.