THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

White Paper on Analysis Services Tabular Large-scale Solution #ssas #tabular

Since the first beta of Analysis Services 2012, I worked with many companies designing and implementing solutions based on Analysis Services Tabular. I am glad that Microsoft published a white paper about a case-study using one of these scenarios: An Analysis Services Case Study: Using Tabular Models in a Large-scale Commercial Solution. Alberto Ferrari is the author of the white paper and many people contributed to it. The final result is a very technical document based on a case study, which provides a level of detail that I don’t see often in other case studies (which are usually more marketing-oriented).

This white paper has the following structure:

  • Requirements (data model, capacity planning, client tool)
  • Options considered (SQL Server Columnstore Indexes, SSAS Multidimensional, SSAS Tabular)
  • Data Model optimizations (memory compression, query performance, scalability)
  • Partitioning and Processing strategy for near real-time latency
  • Hardware selection (NUMA analysis, Azure VM tests)
  • Scalability tests (estimation of maximum users per node)

If you are in charge of evaluating Tabular as analytical engine, or if you have to design your solution based on Tabular, this white paper is a must read. But if you just want to increase your knowledge of Analysis Services, you will find a lot of useful technical information. That said, my favorite quote of the document is the following one, funny but true:

[…] After several trials, the clear winner was a video gaming machine that one guy on the team used at home. That computer outperformed any available server, running twice as fast as the server-class machines we had in house.

At that point, it was clear that the criteria for choosing the server would have to be expanded a bit, simply because it would have been impossible to convince the boss to build a cluster of gaming machines and trust it to serve our customers.  But, honestly, if a business has the flexibility to buy gaming machines (assuming the machines can handle capacity) – do this.

Owen Graupman, inContact

I want to write a longer discussion about how companies are adopting Tabular in scenarios where it is the hidden engine of a more complex solution (and not the classical “BI system”), because it is more frequent than you might expect (and has several advantages over many alternative approaches).

Published Thursday, June 5, 2014 11:49 AM by Marco Russo (SQLBI)



Ola Ekdahl said:

Wow! This is fantastic. I stayed up way past my bedtime to finish reading this. A+

June 5, 2014 9:03 AM

Henko said:

Another great contribution to the community, thanks guys.

June 5, 2014 9:31 AM

Marco Russo (SQLBI) said:

Ola: thank you, and I'm sorry for your sleepness night!

Henko: you're welcome!

June 5, 2014 10:30 AM

kinzleb said:

"We built a set of tools to simplify database provisioning by using the programmability features of SSAS using XAML scripts. This approach gives us a quick way to move a business unit database from one machine to another. Moreover, by using XAML, no human intervention is needed when onboarding new customers: all provisioning is fully automated."

Do you mean XMLA scripts (rather than XAML) in the above paragraph taken from the white paper?  Am curious about the automation process you used for this.

This was a good read, thanks!

June 10, 2014 1:51 AM

Marco Russo (SQLBI) said:

There are C# programs that generates the required XAML scripts. We cannot share them, but you can find some examples in our book (but I would suggest you using Tabular AMO library now, which was not available when we wrote the book:

June 10, 2014 2:18 AM

Nick Singleton said:

Marco -

I have the use case of creating a tabular model which combines 1000s of homogeneous Oracle data sources.  Would it be best to create 1000s of tabular models and then 1 model that combines them together?  I believe with Analysis Services cubes you can do this.  Is this also possible with Tabular?



December 3, 2014 8:55 AM

Marco Russo (SQLBI) said:

It depends. If you want to create only one model, you can create a single database. You might have to automate the process - but is you want to create a single data model, I would consider doing some ETL/view to create a lower number of source tables, instead of having 1000 partitions.

December 3, 2014 11:40 AM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement