Another one of my experiences recently was this strange looking query generated off my Power BI reports. These reports were running off live a live connection to a tabular model on Analysis Services. And of course these queries at up my CPU, leaving it at 100% for quite a long while, with the reports not responding. One thing about the reports were that they had quite a number of parameters (which of course were a client requirement which we couldn’t do much about).
Here’s just a tip of the ice berg sample of the huge query that is generated:
The data model was quite a large one with 30+ measures, and 10+ dimensions, and one thing that we had missed out was that all numeric fields that were not used as measures such as the Year, MonthNumber and WeekNumber fields of the Calendar dimension, and EmployeeAge field of the Employee dimension had their Summarize By property set to Default. But this was not indicated in any way in Power BI especially in the Fields section, where usually fields that are summarized show up with a Σ to indicate that the field is summarized when the data model is a Power BI data model.
The phenomenon is explained in detail in Pinal’s SQLAuthority. Make sure you take a look. Happy New Year!
One interesting issue that showed its ugly head in my recent project was an unexpected exception that popped up on all visuals on a Power BI report. How it happened was quite strange; one moment all the reports were working fine. The next moment when a report visual was modified and deployed to Power BI, all the visuals start turning into a grey box with a cross, with a “See details” link. The details weren’t at all helpful. Just, “An unexpected exception occurred.”:
Clicking on the “Copy Details” link and pasting on a text editor, and quickly scanning through showed this:
And to make matters worse, all subsequent report loads, including that of other reports, puts the reports into hang, until Analysis Services is restarted. The cycle starts again when the offending report is opened again.
A little digging around pointed at a bug on SQL Server 2016 SP1 Analysis Services. I’m not sure if the problem occurs on a version prior to SP1, however there is a fix: SP1 CU5 (KB Article: KB4037354). Once installed the problem disappears. I tested the issue out on SQL Server 2017 Analysis Services, and this problem does not occur on SQL Server 2017.
We’ve all seen how the world of data has been changing during the recent past. Many organizations have massive amounts of data. And many of them are running out of space to put them in. So naturally they turn to the cloud to store and process all of this data. The processed data can then be used for gaining insights in various ways. Apart from the popular forecasting and machine learning that is becoming a fad these days, there is a lot of traditional and “business” analytics that businesses still want to see. Business users want to dive into their data and perform self-service analytics and do data discovery.
However when you looked at the space on the Microsoft cloud, along with its data and analytics capabilities, you have the tools and services to store and process large amounts of data, but what you did not see was something that you could create a analytical model out of so that business users could easily consume as part of their business intelligence routine. Of course you had Power BI, but that was more of a next step, plus Power BI is lightweight and cannot handle more than 10GB.
The closest we had, on the cloud, was to build an Azure VM with SQL Server installed on it, and build the analytic model using Analysis Services. But then there was licensing, and the maintenance overhead among other things that did not make it a feasible option in a lot of cases.
And then Microsoft announced Azure Analysis Services a few months ago, a fully managed Platform-as-a-Service for analytic modeling. And suddenly there was hope. You no longer needed to write complex SQL against a SQL Data Warehouse, nor did you have to import processed data in its hundreds of thousands into Power BI to create your own analytic model.
Azure Analysis Services is currently in its preview phase, and hence Microsoft has given it only Tabular capability for the time being, with Multidimensional hopefully coming some time later. In my opinion that is just fine. One more thing though, if you would remember, the on-prem version of Analysis Services uses Windows Authentication only, in other words you needed to be on a Active Directory domain. So on Azure, in order to access Azure Analysis Services you need to be on Azure AD.
Let’s take a look at quickly setting up your first Azure Analysis Services database.
Creating a service instance is the usual process: Type in “Analysis Service”, and you would see it showing up in the Marketplace list:
Analysis Services on the Marketplace
Once you select Analysis Services, you would see the Analysis Services information screen:
Analysis Services information blade
And then all you need to do this supply the configurations/settings and you are done:
Settings/Configurations blade
When in Preview
At the time of writing, Analysis Services (preview) is only available in the South Central US and West Europe regions, so make sure that your resource group is created on one of those regions. The preview currently offers three standard pricing tiers, and one developer pricing tier (at an estimated cost of ~50 USD per month). The developer tier with 20 query processing units or QPUs (the unit of measure in Azure Analysis Services) and 3GB of memory cache, is ideal to get started. More info on QPUs and pricing here.
Identity Not Found error
Another problem that I ran across was the “Identity not found” error that comes up a few moments after I click on the “Create” button, and Azure starts provisioning my service. It claimed that the user specified under “Administrator” cannot be found in Azure Active Directory, even though I did create such a user in AAD. The reason for this and how to resolve it is documented nicely here by @sqlchick. If you need further details on how to get your Office 365 tenant linked with your Azure subscription while integrating your Azure AD, you should definitely look at this.
Once provisioned, you can pause Analysis Services when it is not being used so that you could save dollars, while switching among pricing tiers is expected in the future.
Okay, now this could be a completely nuh-uh topic. But in the real world these things can and do happen. Example: There is this cube developed a couple of years ago, deployed on various environments such as QA, UAT, Production etc. Then something goes wrong when some new dimension data has text that is longer than what the field allows – ETL fails. You figure it is a small change to the dimension table: field QuarterName on table DimDate needs to be varchar(15) instead of the varchar(9) that it is. So you go fix it directly on the database – Big mistake when it comes to ALM etc. However, the ETL works. But, when you process the dimension on SSAS you get an error:
Errors in the back-end database access module. The size specified for a binding was too small, resulting in one or more column values being truncated. Errors in the OLAP storage engine.
Data Maul 