Category

Data & AI

SSAS Tabular Optimisation In 5 Easy Steps

By | Data & AI | No Comments

SSAS Tabular Optimisation

A well-designed SSAS tabular model is often the key ingredient in a successful analytics solution. It aids the business in delivering the type of ad-hoc analysis and on-the-fly insights that drives performance improvement and economic benefit.  However, not all models are well-designed. Indeed, a poor performing cube can quickly become a burden for any organisation, negatively impacting quality of analysis and becoming a drain on valuable business resources. That’s why SSAS Tabular optimisation is so crucial for businesses wanting to get the most value out of their analytics solution.

Recently, I consulted for a large electrical merchandising business who were having some trouble with their SSAS tabular models. With national operations, it was imperative that their cubes could rapidly and reliably deliver the analysis needed for the business to confidently make strategic decisions around sales, purchasing and inventory. Memory issues and ambiguous design principles were proving to be a challenge in getting the tabular model to behave, and it was clear that I needed to tune the existing cubes with some simple optimisation techniques.

When attempting SSAS Tabular optimisation, I employ a straight-forward 5-step strategy:

  1. Examine the model and remove unnecessary tables
  2. Examine the tables, remove unnecessary columns and edit table structure/content
  3. Examine the columns and change data types
  4. Examine the DAX measures and edit expressions
  5. Examine server properties and edit memory settings

This 5-step performance tuning approach guarantees that tabular model issues can be precisely identified and appropriately addressed.

1.      Examine the Model

A concise tabular model is one that performs best. Therefore, the first step is to review the model itself. Very often a poor-performing cube contains unnecessary tables or relationships that provide no real value. A thorough review of what tables are present in the model and what value they bring will uncover what is necessary and what is redundant. Talking to stakeholders about what they need will also help determine what tables should go and what needs to stay. In my example, I was able to reduce the cube size by removing unnecessary dimension tables that I discovered the business was no longer interested in. This redesign process typically yields ‘quick-and-easy’ wins in terms of cube performance, as it is the easiest to implement.

Figure 1. Removing unnecessary tables reduces SSAS tabular model complexity

 

2.      Examine the Tables

What data actually goes into the tables will ultimately determine the quality of the tabular model. Similar to the first step, a review of the tables will often uncover unnecessary columns that do not need to be loaded into the model. For example, columns that are never filtered on or contain largely null values. Table structure is also important to tabular model performance, as it can affect how much data needs to be loaded. For example, you could reduce the row count of the sales fact table by aggregating it to be at the invoice level, instead of invoice line level. Such a reduction in size will mean that less memory is required by the cube.

Figure 2. Tidy up tables by removing columns, and reducing rows

 

3.      Examine the columns

A crucial aspect of cube performance is compression. Columns with certain data types, or have unique values for all rows will compress badly, and will require more memory. An effective optimisation technique is to correct the data type or value in a column, such that it is able to compress better. Casting values as integers instead of strings or defining decimal points are fundamental practices that are often overlooked in tabular model design, and ultimately come at the expense of performance. In my example, I was able to create a new unique invoice ID that could be used by the business and compressed as an integer. Previously the varchar invoice key was unique at almost every row of the sales table, and was compressing very poorly. The storage engine (Vertipaq) wants to compress columns, and having similar values in the same column greatly aids this. A great tool for this kind of analysis is the Vertipaq Analyzer. This tool can highlight potential areas of interest in compression activities and help track results in terms of cube optimisation techniques.

Figure 3. The VertiPaq Analyzer reveals compression pain points

 

4.      Examine the DAX

For cube users, it is critical that the OLAP queries they run return accurate results rapidly. If a user cannot get the information they need from a model in a reliable or timely manner, the cube is failing to provide the benefits expected of it. Therefore, an important part of tabular model optimisation revolves around the measures, and ensuring that the DAX expressions used are performance optimised for the formula engine. Keeping the measures simple by using basic expressions, and removing complicated filtering clauses means that the measures should perform better. In my example, I was able to change some of the expressions of sales measures at different period intervals (such as month-to-date and year-to-date), such that they could run across different filtering contexts, thus reducing calculation time.

Figure 4. Simple DAX equals better performance

 

5.      Examine the Server

Finally, the biggest factor in tabular model processing performance is the actual memory properties. Depending on the edition of the Analysis Services, there are various levels of memory limits. For the Standard Edition, the 16Gb limit imposed on a single instance of Analysis Services can often be the ‘killer of cubes’. If a reasonable business case exists, then moving to the Enterprise Edition or cloud-based solution can be the right answer to memory woes. However, there are steps that can be taken to get the best out of a SSAS tabular model without abandoning Standard Edition altogether. Increasing the amount of RAM on the server and modifying the server instance memory properties allows you to fine tune processing and reduce the likelihood of memory exception errors. In my example, the cube was failing to process as it would run out of memory during a Full Process. I increased the RAM from 32Gb to 40Gb, and reduced the Total Memory Limits in the server instance properties. With more memory and lower thresholds to which memory cleaner processes were initiated, the cube was able to process in full each time without error.

Figure 5. Fine tune the memory limits to find the optimal level of performance

 

Summary

Like any business asset, a SSAS tabular model loses value when it is not properly configured or utilised. However, with the proper approach methodology, any model can be transformed from an underperforming asset into a valuable resource for a business.

 

If you’re having trouble with SSAS tabular optimisation, we want to hear about it! Please contact us to find out about how we can help you optimise your cubes.

Data & AI Strategy metrics

By | Data & AI | No Comments

Why are Data & AI strategy metrics important? The beauty of “strategies” for some is that a strategy – unlike a tactic – often doesn’t come with any clear success / fail KPI’s. It allows a lot of wriggle room for ambiguous assessments of whether it worked or not. However any self-respecting Data & AI strategy should not allow this. After all, it is designed and executed in the name of improving the use of data and measurable outcomes within an organisation. A good Data & AI strategy should have measures to determine its success.

Data & AI Strategy metrics that matter

Commonly raised metrics are based around uptake and usage (software vendors are particularly fond of these). This seems based on the hope that the apparent usage of tools is inherently a good thing for a company that will somehow lead to – I don’t know – increased synergy?

Dilbert Utilising Synergy

Dilbert Utilising Synergy

Sometimes they are measured around data coverage by the EDW or project completion.  However, if I was to put my CEO hat on, I would want to know the answer to the question “how are all these Data & AI users improving my bottom line?”. After all, if the Data & AI tools are being heavily used, but only to manage the footy tipping competition, then I’m not seeing a great deal of ROI.

The metrics that matter are the Corporate metrics.

A good Data & AI Strategy should be implemented with a core goal of supporting the Corporate strategy, which will have some quantifiable metrics to align to. If not, a good Data & AI strategy isn’t going to help you much as your organisation has other problems to solve first!

In a simple case, imagine a key part of the strategy is to expand into a new region. The Data & AI strategy needs to support that by providing data & tools that supports that goal, enabling the team in the new region to expand – and should be measured against its ability to support the success of the Corporate strategy.

This is why at FTS Data & AI, our first step in defining a Data & AI Strategy for an organisation is to understand the Corporate strategy – and its associated metrics – so we can align your Data & AI strategy to it and create a business case to justify why you need to embark on a Data & AI strategy in the first place. The metrics are the foundation that prove that there is deliverable value to the business. This is why the Corporate Strategy sits at the top of our Strategy Framework:

Data & AI Strategy Framework

Data & AI Strategy Framework

We have extensive experience designing strategies that support your business. Contact us today to speak with one of our experts.

Agile Zero Sprint for Data & AI projects

By | Data & AI | No Comments

Agile methodologies have a patchy track record in Data & AI projects. A lot of this is to do with adopting the methodologies themselves – there are a heap of obstacles in the way that are cultural, process and ability based. I was discussing agile adoption with a client who readily admitted that their last attempt had failed completely. The conversation turned to the concept of the Agile Zero Sprint and he admitted part of the reasons for failure was that they had allowed Zero time for their Agile Zero Sprint.

What is an Agile Zero Sprint?

The reality of any technical project is that there are always certain fundamental decisions and planning processes that need to be gone through before any meaningful work can be done. Data Warehouses are particularly vulnerable to this – you need servers, an agreed design approach, a set of ETL standards – before any valuable work can be done – or at least without incurring so much technical debt that your project gets sunk after the first iteration cleaning up after itself.

So the Agile Zero Sprint is all that groundwork that needs to be done before you get started. It feels “un”-agile as you can easily spend a couple of months producing nothing of any apparent direct value to the business/customer. The business will of course wonder where the productivity nirvana is – and particularly galling is you need your brightest and best on it to make sure you get a solid foundation put in place so it’s not a particularly cheap phase either. You can take a purist view on the content from the Scrum Alliance or a more pragmatic one from Larissa Moss.

How to structure and sell the Zero sprint

The structure part is actually pretty easy. There’s a set of things you need to establish which will form a fairly stable product backlog. Working out how long they will take isn’t that hard either as experienced team members will be able to tell you how long it takes to do pieces like the conceptual architecture. It just needs to be run like a long sprint.

An Agile Zero Sprint prevents clogged pipes

An Agile Zero Sprint prevents clogged pipes

Selling it as part of an Agile project is a bit harder. We try and make this part of the project structure part of the roadmap we lay out in our Data & AI strategy. Because you end up not delivering any business consumable value you need to be very clear about what you will deliver, when you will deliver it and what value it adds to the project. It starts smelling a lot like Waterfall at this point, so if the business is skeptical that anything has changed, you have to manage their expectations well. Be clear that once the initial hump is passed, the value will flow – but if you don’t do it the value will flow earlier to their expectations, but then quickly after the pipes will clog with technical debt (though you may want to use a different terminology!)

BI User Personas – are you scaring users with the kitchen sink?

By | Data & AI | No Comments

BI User Personas are a key part of delivering any BI solution. Throughout my career I have encountered clients who have all faced the problem that their BI Solution has not achieved the adoption they had hoped for. This in turn has reduced the impact of the solution and thus the ROI. A common thread in the examples I have seen is the horrifying kitchen sink that is thrown at every user.

 The wrong BI User Persona causes some interesting reactions

The kitchen sink is scary for some!

To explain to those not familiar with the idiom, to include “everything but the kitchen sink” means to “Include just about everything, whether needed or not”. What it means in this context is that the BI solution presents so many dimensions, measures and KPI’s to the user that the experience becomes confusing, overwhelming and as a consequence – useless.

Why building your BI solution is like making a hit movie.

No Hollywood movie is ever made without considering the audience appeal – they even use predictive analytics to drive scripting decisions. So why should your project be any different? You have consumers that need to be satisfied, and their wishes must be taken into account.

A key element of our Data & AI Strategy is to ensure that the end users different needs are planned for. Constructing BI User Personas to define what level of detail gets exposed to each persona helps in this process. To stretch our analogy a little further, your executive team may only care that there *is* a kitchen sink and whether it is working or not. A management team may need to know how hot the water is and how water efficient the tap is. The analysts will need to know detailed water usage statistics over time for analysis. Not everyone needs to know the same thing.

Most BI tools allow you to provide different views of the data model so that you can tailor the output of a very complex model to users with simple needs. An executive may only need a few key metrics and dimensions in a dashboard to examine before they pass further analysis downstream. A manager may need a more complex report with interactivity to drill into an issue. The analyst may simply need access to raw data to answer their questions.

The same applies for less data literate users. If they are not technically minded they may find a much simpler model less intimidating. Data literacy is whole additional topic but with proper preparation, it can be managed and taught.

BI User Personas drive a smash hit!

Understanding your audience is essential. As part of the process of designing your solution, BI User Personas need to be defined so they get appropriately suited content.

Building and understanding the personas of your end user team is of course only part of the equation. There are many human components in a Data & AI Strategy that need to be implemented. Change management, training and ongoing communication help ensure that what you deliver is adopted, and part of the strength of FTS Data & AI is that as part of the FTS Group we can bring our stablemate Cubic in to help with this.

BI Project Success

By | Data & AI | No Comments

What makes for BI Project Success? I read with interest the results from the BI Survey 2018 – particularly its results on the subject of Success Factors in implementing BI Applications. I took particular interest in two interlinked themes, speed and competency of implementation.

Speed of Implementation

Speed is a critical part of achieving BI project success. It was very clear from the results that Enterprise BI Platforms took over 2 times as long to implement as Self Service solutions. This in itself isn’t overly surprising. An Enterprise platform is typically selected because the problem it is trying to solve is more complex. Self Service BI Solutions work excellently for targeted problem solving but in my experience struggle as the data landscape gets more complicated.

However what I thought was the most interesting finding was that the longer a program runs, the harder it is for it to deliver value. This seemed to be a universal effect regardless of project type or expected value. This led the survey takers to the conclusion – that I agree with – that smaller, more focused projects are more likely to deliver value. This is why we embrace Agile delivery methodologies at FTS Data & AI.

Competency of implementation

Tying in with speed of implementation is the competency of it. The more capable the company was in delivering BI programs, the faster they delivered – by a factor of over three times. Shorter implementations had less issues in delivery as well. This could well be a reflection of the higher competency teams delivering results more quickly and capably, resulting in better BI project success.

Implementation time by best-in-class companies in median months

Implementation time by best-in-class companies in median months (credit: BARC)

The competency was also impacted by the support from vendors and implementers. Vendor support had a big impact on project outcomes, with good support correlating well with project outcomes. This works the other way as well – poor support led to worse outcomes, so tool selection criteria clearly should look at local support. I would draw again on my comments on the Australian experience with Microstrategy, where I have had customers move away from that platform due to an inability to get local support for it.

Implementers also had a significant impact – projects with excellent partner support did significantly better than those without. It is also worth noting that the wrong choice of partner can lead to outcomes that are actually worse that using no partner at all. The survey team advised picking a specialist partner over a generalist firm – which I believe ties in to the above effect of vendor support – some vendors rely heavily on partners to deliver on their behalf (e.g. Microsoft) so when choosing a partner, a strong track record with your chosen vendor platform should be a key criteria.

They also advise road testing partners with a proof of concept. I support this approach as a successful relationship with a partner needs to be evaluated and there’s nothing quite like getting hands on together to properly evaluate their competency, commitment and understanding of your specific needs.

Takeaways for BI Project Success

My key takeaways from these survey results are:

  • Focus on smaller delivery cycles for better outcomes
  • Ensure vendor support is a key factor in tool decisions
  • If you need help, the right delivery partner will make a big difference