A Fine Slice Of SQL Server
Chris Webb looks at SSAS in Azure:
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.
I hope so. Lack of multidimensional isn’t a deal-killer, but it’s a deal-harmer.
Comments closedChris Webb shows how to create nested variables inside DAX:
There aren’t any performance benefits to doing this, although of course it helps with code readability and organisation (thanks to Marius for confirming this).
Even so, click through to see an example of how to do this.
Comments closedAttempting to use the AS Processing Task results in the following error: “[Analysis Services Execute DDL Task] Error: This command cannot be executed on database ‘MySSASDB’ because it has been defined with StorageEngineUsed set to TabularMetadata. For databases in this mode, you must use Tabular APIs to administer the database”
The reason for keeping SSAS processing in an SSIS package was because it kept consistent logging throughout their data refresh process. So we set out to find another solution.
Read on for the explanation and the solution.
Comments closedChris Webb shows how to define variables in DAX:
Variables are the best thing to happen to DAX since, well forever – they are so cool I’m almost ready to like DAX as much as I like MDX. There are already several good articles and blog posts out there describing how to use them (see here and here), but I was looking at a Profiler trace the other day and saw something I hadn’t yet realised about them: you can declare and use variables in the DEFINE clause of a DAX query. Since my series of posts on DAX queriesstill gets a fair amount of traffic, I thought it would be worth writing a brief post showing how this works.
There are some limitations, but Chris shows a way of getting around one of them.
Comments closedBefore I explain how to fix this, let’s take a look at what’s happening behind the scenes.
When jdoe@adventureworks.com opens the dashboard a connection string is created including the effectiveusername property, which is expected behavior.
The value specified for this property is jdoe@adventureworks.com.
The connections string including the queries are sent via the On-Premises gateway to the SSAS server that hosts the data needed to view the report.
Once the connection is established, using the username and password specified in the Data Source settings, all queries are executed usingjdoe@adventureworks.com.
Read on for the solution.
Comments closedJens Vestergaard shows how to implement continuous deliver with Analysis Services cubes:
None of the above mentioned scenarios appeals to Team Foundation Server(TFS) and in order to get into the no-sweat zone during release time, we need to build our deployments around TFS; The obvious choice when working with Microsoft.
Natively Visual Studio, or more precisely MSBuild, does not support dwprojfiles which are used for Analysis Services (SSAS) projects. So obviously this has to involve some kind of magic. But as it turns out, it’s not all that magic. However there is not much documentation on this particular scenario out there but I managed to find one good resource, which is this. It gave me just enough assistance to complete the task.
This is a long post, but well worth reading.
Comments closedBill Anton has a method for understanding large tabular models on legacy platforms:
Unfortunately, the Tabular Model Explorer is only available for 2016 (compatibility 1200) tabular models – which many folks haven’t moved over to just yet (despite the overwhelming list of reasons why SQL 2016 is one of the best releases in a very long time).
Those of us stuck with 2012/2014 environments have no other option than to comb through the diagram view for that one table we’re looking for…or scan the unordered list of tables/columns in grid view, or arrow-key through a bunch of cells in the calculation pain pane to find a particular measure… or so I thought up until a few weeks ago when I discovered a better way!
At least 60-70% of the DBA population would chafe at the idea that is a “legacy” platform, so maybe I’m overstepping it a little with calling 2014 legacy. But seriously, 2016 is a huge improvement, well worth the jump.
Comments closedMeagan Longoria shows how to document a tabular model using Excel and Power BI:
The Database Info tab uses cube functions to display the values shown. The rest of the tabs use pivot tables and slicers.
You can download and use this model if you have Excel 2016 or 2013 with Power Query. Just download the file, change the values in the TabularInstanceName and TabularDBName queries, and refresh. I was able to open the file and update the queries with no issues and no changes in Excel 2013.
This is great if you just need documentation or a quick way to look up specific information. But we could really analyze this using visualizations other than tables in Power BI Desktop.
That’s pretty fancy. Check it out.
Comments closedThis query has something in it – I don’t know what – that means that it cannot make use of the Analysis Services Storage Engine cache. Every time you run it SSAS will go to disk, read the data that it needs and then aggregate it, which means you’ll get cold-cache performance all the time. On a big cube this can be a big problem. This is very similar to problems I’ve seen with MDX queries on Multidimensional and which I blogged about here; it’s the first time I’ve seen this happen with a DAX query though. I suspect a lot of people using Power BI on SSAS Multidimensional will have this problem without realising it.
This problem does not occur for all tables – as far as I can see it only happens with tables that have a large number of rows and two or more hierarchies in. The easy way to check whether you have this problem is to refresh your report, run a Profiler trace that includes the Progress Report Begin/End and Query Subcube Verbose events (and any others you find useful) and then refresh the report again by pressing the Refresh button in Power BI Desktop without changing it at all. In your trace, if you see any of the Progress Report events appear when that second refresh happens, as well as Query Subcube Verbose events with an Event Subclass of Non-cache data, then you know that the Storage Engine cache is not being used.
This doesn’t look to be a quick fix, so do read the whole thing to help figure out how to avoid this issue.
Comments closedMeagan Longoria notes that last processed date is UTC but the Properties page is local time:
The datetime returned by this query is in UTC. My query returns 9/19/2016 7:43:03 PM.
If I go into the properties of my SSAS database, I can see this same info, but the timezone conversion has already been done for me (this server is in Central time zone).
I think that on net, that’s the best way to do it: store everything in UTC and use the presentation layer to convert those to local times.
Comments closed