SSAS Cube Design and Optimisation

Key Points

  • A robust data model is the first steps towards an efficient cube
  • Functional dependency what? If you don't know your functional dependencies from appendices then you may be missing some vital attribute relationships
  • A one day health check for your cube could save you many days of frustration and provide real value for your users

Is you Analysis Services (SSAS) cube underperforming? Our SSAS optimisation service will identify your cube performance issues whilst you learn about the many optimisation techniques available. Training and development all in a single day of consultancy.

About CMBI

Billion row cubes!

Analysis Services cubes can scale to well over a billion rows while still allowing users to execute ad hoc queries and requests through MS Excel and many other user tools. With the potential to have all your data at your fingertips it is no wonder that Analysis Services is so popular.

But poor performance can be the deathnail for a business intelligence solution and time strapped users will be unsympathetic to technical excuses. The good news is that it is possible to make significant improvements to the performance of any SSAS cube if you know where to look. The CMBI SSAS bootcamp will give your cube the health check it needs to really impress your users.

Getting under the hood of SSAS

We check all aspects of your cube to ensure it is providing the best performance and usability experience for your users. Building large cubes can be complex so a second opinion of your cube design will likely saves you hours of frustration and give you the reassurance that you are getting the best from the technology.

We look at the four fundamental aspects of an Analysis Services cube: logical model, physical model, behavioural model, and presentation model

Logical model dimensions

The definition of attributes, measures, relationships, and hierarchies all contribute towards query and processing time performance.

Physical model

Indexes, partitions, aggregations, data types, and storage mode configuration can make a massive difference to the ability of the cube to scale up and support many concurrent users.

Behavioural model

The MDX script is where you define your calculations, sets and business rules. Getting your MDX script right can mean the difference between queries taking many minutes and executing immediately.

Security is important but you need to understand the implications for performance.

KPIs, and dimension/attribute types also contribute to usability and performance.

Presentation model

Display folders, formats, perspectives, translations, and actions all make the cube more usable. If you are not exploiting all these features you could be missing out on some great opportunities to impress your users.

Why Analysis Services (SSAS)?

Analysis Services makes it easy for end users and analysts to work with very large volumes of data and/or complex data models. Analysis Services data can be manipulated through MS Excel (2003-2010), SharePoint tools including Performance Point and PowerView, Tableau, Reporting Services (SSRS), Strategy Companion Analyzer, and many other great visualisation and reporting tools.

From general ledger to data mining

Analysis Services cubes can be used for straightforward sales analysis or more complex applications like General Ledger, or Budgeting and Forecasting, and data mining. Analysis Services has built in metadata support for a whole range of typical analytical business scenarios.

No more manual reports

Analysis Services is frequently used to automate existing Excel reports and spreadsheets that were previously thought too complex to implement with a traditional reporting tool. With Analysis Services the report owner continues to work within the Excel environment but avoids all the manual effort associated with the extracting data and reformating reports each period.

Why CMBI for Analysis Services (SSAS)?

CMBI's Enterprise BI Architect, Colin McGowan, has been using SSAS since it's release in 2005. He is recognised by his peers as a leading expert in SSAS cube design and MDX scripting. See Who's who in Data Warehousing and Business Intelligence. CMBI have undertaken many Analysis Services design and optimisation engagements in complex and demanding environments from market research and media to financial services and banking. Contact us today to book in your one day SSAS performance health check.

About CMBI

CMBI designs and develops business intelligence and analytics solutions for clients in Sydney and regional NSW. CMBI technology expertise includes Power BI, Power Pivot, Excel, SQL Server, Analysis Services, SharePoint BI, and Tableau.


See Also