Category Archives: SSAS

How and When to Scale Up/Out Using Azure Analysis Services

Some of you may not know when or how to scale up your queries or scale out your processing. Today I’d like to help with understanding when and how using Azure Analysis Services. First, you need to decide which tier you should be using. You can do that by looking at the QPUs (Query Processing Units) of each tier on Azure. Here’s a quick breakdown:

  • Developer Tier – gives you up to 20 QPUs
  • Basic Tier – is a mid-scale tier, not meant for heavy loads
  • Standard Tier (currently the highest available) – allows you more capability and flexibility

Let’s start with when to scale up your queries. You need to scale up when your reports are slow, so you’re reporting out of Power BI and the throughput isn’t working for your needs. What you’re doing with scaling up is adding more resources. The QPU is a combination of your CPU, memory and other factors like the number of users.

Memory checks are straightforward. You run the metrics in the Azure portal and you can see what your memory usage is, if your memory limited or memory hard settings are being saturated. If so, you need to either upgrade your tier or adjust the level within your current tier.

CPU bottlenecks are a bit tougher to figure out. You can get an idea by starting to watch your QPUs to see if you’re saturating those using those metrics and looking at the logs within the Azure portal. Then you want to watch your processor pool job que length and your processing pool busy, non-IO threads. This should give you an idea of how it’s performing.

For the most part, you’re going to want to scale up when the processing engine is taking too long to process the data to build your models.

Next up, scaling out. You’ll want to scale out if you’re having problems with responsiveness with reporting because the reporting requirements are saturating what you currently have available. Typically, in cases with a large number of users, you can fix this by scaling out and adding more nodes.

You can add up to 7 additional query replicas; these are Read-only replicas that you can report off, but the processing is handled on the initial instance of Azure Analysis Services and subsequent queries are being handled as part of those query replicas. Hence, any processing is not affecting the responsiveness of the reports.

After it separates the model processing from query engine, then you can measure the performance by watching the log analytics and query processing units and see how they’re performing. If you’re still saturating those, you’ll need to re-evaluate whether you need additional QPUs or to upgrade your tiers.

Something to keep in mind is once you’ve processed your data, you must resynchronize it across all of those queries. So, if you’re going to be processing data throughout the day, it’s a good idea not only to run those queries, but also to strategically synchronize them as well.

Also important to know is that scale out does require the Standard Edition Tier; Basic and Developer will not work for this purpose. There are some interesting resources out there that allow you to auto scale. It will be based on a schedule using a PowerShell runbook. It uses your Azure automation account to schedule when it’s going to scale up or out based on the needs of the environment. For example, if you know Monday mornings you’re going to need additional processing power to run your queries efficiently, you’ll want to set up a schedule for that time and then you can scale it back.

Another note is that you can scale up to a higher tier, but you cannot scale those back automatically if you’re running a script. But with this ability it does allow you to be prepared for additional requirements in that environment.

I hope this helped with questions you have about scaling up and out.

 

Power View Date Filter Troubles

Now that I’m doing some more development of dashboards in the Power View interface, I thought it might be helpful to post about some of the date “gotchas” I’ve run in to with the development interface.  Power View is very nice about handling Date filters if you are able to specify a date you’d like to use as a start point, end point, or 2 dates to post data about between, however, if you’re looking to determine if a date range is say, “This Week”, “This Month”, “This Quarter”, etc… you’ll have quite a bit more trouble.  The workaround I’ve found to solve this issue is to create a new column that results in a boolean (true/false) answer.  Then within your Power View filtering, you are able to choose “IsPastWeek” = True, etc.  For Example, in your formula bar (or whatever tool you’re using, excel, powerbi, or SSAS tabular):

The above formula, simply put, says If the date is later than today minus 7 days, then return true, else, return false.  Below is a view of the filters in my Power View Dashboard:

By setting this filter, the ability to look at specific dates or time periods makes it easier to report graphically.  Also, the ability to filter on separate objects within the same canvas allows us to easily compare similar time periods side by side.

Some other options for comparing date ranges are:

IsCurrentMonth =if(MONTH(Table[Date])=MONTH(NOW()),TRUE,FALSE)

IsCurrentYear =if(YEAR(Table[Date])=YEAR(NOW()),TRUE,FALSE)

And many more can be found here: Time Intelligence in Power Pivot in Excel