Multidimensional and Tabular Compared

Microsoft's SSAS offers a choice of technology for OLAP cubes - the original Multidimensional, and the newer, Tabular. I compared them back in 2012 http://richardlees.blogspot.com.au/2012/05/sql-server-2012-tabular-versus.html. It is now safe to assume now that Tabular is the strategic technology. Not officially strategic, but with the dearth of Multidimensional enhancements since 2012 and Tabular recently becoming the default install, it is clear to see where Microsoft is heading.

It has been pleasing to see enhancements to Tabular in each SQL release, which has reduced the need for Multidimensional. However, there are still a few features that Tabular does not support, necessitating new developments with Multidimensional. The standout feature that Tabular does not support is composite primary keys. Ralph Kymball, needs to take some responsibility. His Star Schema modeling methodology employs single column surrogate keys for all dimension tables. Kymball has advanced data modeling a great deal, but I think it's wrong to blindly generate surrogate keys when natural primary keys exist. Kymball methodology requires fact and dimension tables to be physically generated from underlying normalized tables. This involves a lot of work, reconciliation and performance cost, when the underlying tables could be used directly, with their composite primary keys.

The really sad fact is SSAS documentation, comparing Multidimensional and Tabular, does not even list composite keys as a feature worth comparing. It's as if, no one would require a cube technology supporting composite primary keys. See https://docs.microsoft.com/en-us/sql/analysis-services/comparing-tabular-and-multidimensional-solutions-ssas

Since SQL 2012, I have chosen Tabular where possible, but it has been on rare occasions that Tabular could succeed. Mostly due to Tabular not supporting composite keys, and my unwillingness to generate redundant star schemas. Why would you generate copies of fact and dimension tables, just to build surrogate keys? Surrogate keys have no meaning. Business keys have meaning, and are typically part of the table already. Also, when you generate a redundant star schema, you need to continually append (or rewrite) to these tables as your DW data arrives. And, you should continue to reconcile these generated tables with the source tables. One might argue that the resultant star schema is more efficient than a star schema with composite keys, but that argument is marginal and rarely overcomes the increased cost and lag of building star schemas. And there can be significant performance loss in flattening (denormalising) the snowflake dimensions.

Even something simple like a Type 2 dimension already has a perfectly good natural key consisting of the source system's business key (eg ClientId) and EffectiveFromDate. So, why not just add EffectiveFromDate_Client to the fact table? A complete dimension and fact table rewrite is unnecessary!

I'm hoping that data warehouse developers see the sense of natural composite keys and that Tabular supports them in the near future. In the meantime, I will only be creating Tabular models on the rare occasions where composite keys are not part of the data structures.

If you would like to see Tabular support composite keys, please vote on this connect item. https://connect.microsoft.com/SQLServer/feedback/details/3144082

For a performance comparison of Tabular versus Multidimensional see https://richardlees.blogspot.com/2019/08/aws-redshift-performance-versus-sql.html

Relative Dates, Today, Yesterday, This Week, Last Week etc

A common requirement in Business Intelligence is for reports on relative dates, such as

• Today,
• Yesterday
• Same Day Last Year
• This Week
• Last Week
• This Period
• Last Period
• This Year
• Last Year

Where these are dates relative to today on the fiscal calendar. It is possible to create these members on the Time Intelligence dimension. However, since they are so widely used, and often in combination with other Time Intelligence members, there is better performance and better functionality if these members are in the time dimension. 

Putting them in the time dimension is relatively easy, all you need is an existing time dimension that has Date as a level of granularity and the ability to use a view (or the SSAS dsv) to create additional date attributes.

In your Date view, simply 

1. Add in a column for RelativeDay, another column for RelativeWeek etc. This column will have the "relative day" value. For example, 
• CASE 
•  WHEN CONVERT(DATE, GETDATE())=MyDateTable.TheDate THEN 'Today'
•  WHEN CONVERT(DATE, GETDATE()-1)=MyDateTable.TheDate THEN 'Yesterday'
• ELSE 'Other Days'
• End RelativeDay
2. Add in additional WHEN clauses for Day Before Yesterday, Same Day Last Year, Yesterday, Last Year etc if you want them. 
3. Add an attribute for each relative date into the Date dimension. One for RelativeDay, one for RelativeWeek, one for RelativePeriod etc.
4. Then, all you need to do is ensure that the Date dimension is updated early every morning. You are probably updating your dimensions many times per day. By default dimension attributes are flexible. These attributes need to be flexible as they are changing, literally every day.

From then on, cube users and reports can use these relative dates. For example, a classic use would be to have a report that filters on This Week and Last Week, and shows 8-14 columns, one for each day in the last 1-2 weeks. Whenever a user requests this report the default showing will be for the last 8-14 days, without the user having to select the actual date.

Adding these relative dates will not increase the size of your cube. The date dimension will be marginally bigger. These relative dates are easier to use than Time Intelligence and perform much better. Especially better when you want relative dates and Time Intelligence.

Basket Analysis Made Easy

This article is aimed at technologists who hear about Basket Analysis and need a simple explanation with examples. Basket Analysis used to be the preserve of high cost specialist providers. Now we have OLAP technology and hardware that makes Basket Analysis available at commodity prices. Everyone involved in retail analytics should understand Basket Analysis. Essentially, Basket Analysis supports analysis of sales by the combinations of items purchased.

To elaborate, I will provide two classic examples of Basket Analysis.

1. Menu Trim
The business would like to remove a couple of low value items from the menu (product list). A smaller menu generally supports lower operational cost and inventory investment. Candidate items might be the lowest by sales value or gross profit contribution. We observe that the lowest value items are Frozen Yoghurt and Fruit Salad, which we can find by a simple slice of items and their sales/gross profit. However, this does not take into account whether customers come in to buy the product on its own, or whether they are buying lots of other products too. We can see from the Basket Analysis query below, that Frozen Yoghurt customers buy lots of other products with their Frozen Yoghurt. Basket Analysis can sum all the products the customer buys with Frozen Yoghurt, which we call Affected Net Sales. This suggests that we can remove Fruit Salad from the menu we only risk the $15,414 of sales, but if we remove the Frozen Yoghurt, we risk $1,899,151 of sales. We say that it is "at risk" because the customers might be coming in especially for the Frozen Yoghurt, and purchasing other products while there. Without Frozen Yoghurt these customers might go elsewhere for their entire basket.

2. Attracting Large Value Transactions
Your business might have a strategy to increase sales by promoting menu items that tend to be involved in large value transactions. Basket Analysis can help you do this by identifying menu items (product items) involved in large transactions. In the example above, Avocado Toast has a high transaction average of $105, while the average transaction for all items is $45. So, the business strategy might be to discount and/or promote Avocado Toast to attract these high value customers.The marketing geniuses will work out a way to attract these customers, which might be by discounting Avocado Toast, offering a discounted side item, promoting quality/health features, inter-company coupon, etc. Once the campaign starts, Basket Analysis can very succinctly report on the success of the campaign. One of the key indicators will be the transaction average of Avocado Toast transactions during the campaign. The sales of Avocado Toast might increase two fold, but if the Transaction Average is halved, you are probably only attracting customers who purchase Avocado Toast alone. Ie, you are not attracting customers with large transaction value. In the chart below, we can see that the Avocado Toast promotion has been successful in increasing Avocado Toast transactions, however, these additional transactions have been small value transactions. The marketing geniuses might need to try a new strategy!

I hope this has shown how Basket Analysis can help business' create better strategies and measure how well these strategies are working. This is just two simple examples of an infinite number of possibilities, only limited by your creativity. Basket Analysis has become a commodity feature in retail analysis and is becoming an essential tool for successful retail strategies.

Basket Analysis IV - Menu Trim

This is the third post in a series on Basket Analysis. To read the first post go to Basket Analysis I.

Retail stores are always trying to balance range of stocked items with inventory efficiency. A common task is to perform a "menu trim" where low profit contribution items are removed from the menu. A simplistic approach would be to remove the items with the lowest associated sales value or gross profit. However, this can result in trimming items that, while not profitable in themselves, are attracting customers and have high "associated" sales and profit contribution.

Consider the menu items above that are ordered by item sales. The Berry Smoothie is the item with the lowest sales value and gross profit. A simplistic view would be to say let's trim Berry Smoothie off the menu as it only represents $795.23 of sales. However, from our basket analysis query, we can see that the Berry Smoothie has a high Associated Net Sales and Associated Gross Profit. If we took Berry Smoothie off the menu, we might be risking $19,400 of associated sales. This is the total value of all transactions that have included Berry Smoothie. It might be that customers are coming into the store primarily for the Berry Smoothie and getting other items with the smoothie. If we remove Berry Smoothie from the menu these customers might not patronize our store.
Basket Analysis does not answer these questions directly, but it does give us information to make a better informed decision. In the case above, we might consider trimming the Vegeburger from the menu, as it has the lowest Associated Net Sales and lowest Associated Gross Profit. It appears that customers who come in to buy vegeburgers, purchase that item alone. So if that item was trimmed from the menu, it would appear to put less sales at risk.

Relative Dates - Today, Yesterday, LastWeek etc.

A useful little piece of functionality I often add to OLAP cubes is what I call Relative Dates.  In the Date dimension I add attributes CurrentDay, CurrentWeek, CurrentPeriod, CurrentYear etc.  These attributes are simply functions within the Dates dimension view, which gets processed early every morning. For example CurrentDay could be something like
Case
when d.MyDate=convert(date,getdate()) then 'Current Day'
when d.MyDate=convert(date,dateadd(dd,-364,getdate())) then 'Current Day LY'
when d.MyDate=convert(date,dateadd(dd,-1,getdate())) then 'Yesterday'
when d.MyDate=convert(date,dateadd(dd,-365,getdate())) then 'Yesterday LY'
else 'Other days'
end CurrentDay,

Note, Current Day Last Year is -364 days, which will ensure that it will be the same day of week. It's the same as saying -52 weeks.

Note, you only need to process the Date dimension, the large fact tables do not need to be reprocessed, as their Date dimension keys are not changing. It's just the, flexible, attributes of the Date dimension that are changing daily.

Note, it is not practical to create a hierarchy of these attributes as they don't fall within oneanother. For example, Today and Yesterday might be in the same fiscal week, or they might fall in consecutive weeks.

The logic for CurrentWeek etc is a bit more complicated as your case statement will be checking if the FiscalWeek of getdate() is the same as the FiscalWeek of MyDate. Eg.
case
when d.MyWeek=(select dd.MyWeek from tbDates dd where dd.MyDate=convert(date,getdate())) then 'Current Week'
when d.MyWeek=(select dd.MyWeek from tbDates dd where dd.MyDate=convert(date,dateadd(WW,-52,getdate()))) then 'Current Week LY'
when d.MyWeek=(select dd.MyWeek from tbDates dd where dd.MyDate=convert(date,dateadd(WW,-1,getdate()))) then 'Last Week'
when d.MyWeek=(select dd.MyWeek from tbDates dd where dd.MyDate=convert(date,dateadd(WW,-53,getdate()))) then 'Last Week LY'
else 'Other weeks'
end CurrentWeek,

You can do the same sort of logic for Periods, Quarters, and Years. Note, this functionality does not replace Time Intelligence. If anything it adds to it as Time Intelligence will go across the Relative Dates attributes, or, more likely, you can use the Relative Dates attributes to filter dates and use a regular date hierarchy on rows/columns with Time Intelligence. The advantage of the CurrentDay, CurrentWeek, CurrentPeriod, and CurrentYear attributes is that reports can be relative in time. Ie, the filter can be a multi-select of this period and last period, showing weeks on columns, which will have 5-9 weeks.  So the report will always display the last 5-9 weeks, without the user having to select the current period etc. These filters will work in Reporting Services, Excel, PerformancePoint, or any other cube browser/reporting tool.

Signs your Data Warehouse is Heading for the Boneyard

I have come across too many data warehouse projects that have obvious signs of failing with project sponsors in denial.  Here are a few signs that you might look out for and take seriously.

1. You've spent one million dollars and haven't got one million dollars of value. Data warehouse (DW) projects are never really finished, as there are continual changes in business practices, business software, competition, business strategy, regulation, etc. DWs need to be flexible so that they can continually be updated/enhanced to adapt to these changes in the business. If you've spent one million dollars and not got value from that, you are probably developing something that is inflexible. Typically, I would be encouraging, even very large companies not to spend that much money without showing benefit. The vision for a DW might be very large, but start with the trickiest, largest, and/or most crucial data. The DW should manage that data and provide value to the business before more work is commissioned.
2. Your project is 3 months overdue and the development manager is telling you that more resources are required. There are probably fundamental design issues that will continue to cause issues. Such a long development cycle will probably lead to an inflexible DW.
3. Your user community do not trust the output. The DW is producing reports, dashboards, alerts, etc., but the user community do not trust the data and aren't using it to run the business.
4. The same question can be asked to different parts of the warehouse with different answers. One transaction detail should, ideally, be stored in only one place in the DW. If it is stored in mulitple places, the design is probably not optimal, and if they are providing different answers, there is definitely a problem.
5. It's almost impossible to reconcile the data warehouse with your OLTP systems. The ETL (extract, transform and load) processes are so complex and involve so much custom code that no one can reconcile DW facts with OLTP systems. The ETL processes are probably too complex.
6. Your developers are telling you that they are spending a significant amount of time developing functionality that will make the data warehouse more flexible. This is all too common in my view. Developers like to develop, and they can create large generic applications to schedule jobs, "clean data", manage file transfers etc. Most generic DW software is already available in Microsoft's SQL Server and related technologies. The role of the developers is to use this software to create a bespoke application that is as simple as possible. Try and stick to existing vendor software. They will maintain it and provide future releases. You will always have the option to move platforms in the future having spent much less money.
7. The data warehouse requires a full-time person to manage the jobs and exceptions. DW schedules should be reliable enough to manage themselves, simply providing alerts for late data etc. Intervention should only be required where there is a defect in the DW and a diagnosis and change is required. Even when there is a catastrophic failure, the DW should start up and simply continue from where it was.
8. Some of the development team have left before value is delivered. DW projects should be delivered in small releases that provide value. If the release cycle is so long that significant team members are leaving, there is probably an underlying design issue.
9. There are performance issues that are so severe that it is looking difficult to process one day's data in a day. This is a bright red flag.  DW will only want to grow with more feed systems and more data. Such a large performance issue should be seen as a huge question mark over the DW.
   
10. The development team are telling you that they need to spend a lot of time up front to develop software that's going to make the DW more flexible! It's almost a certainty that their software will make the DW more inflexible. Think of it this way, if it takes 50 dev days to create a DW, it will take a proportion of that 50 dev days to make a change.  If the first deliverable takes 1000 dev days, enhancements will tend to be a proportion of 1000 days.

MDX Running totals on a nested ordered set

I like MDX puzzles, and just got a good one today. The requirement was to have running totals over an ordered set, but the set was only ordered at the lowest level, and the running totals were just within the parent member. Ie, a list of countries and cities, where cities are ordered descending by [net sales] within country, and [profit] has a running total for the cities within that country.

As I have said before, anything is possible with MDX, and here is the solution for ordering cities within countries and having running totals of profit of cities within countries. I haven't ordered the countries here, but they could be independently ordered.

Essentially, what the query does is order the cities (along with city.All). For the running total it takes the members from the City.All member to the current member and aggregates them. Simple really.

with
set OrderedCities as
[Geography].[Country].[Country]
*Order(Nonempty([Geography].[City].Members,[Measures].[Profit])
,[Measures].[Net Sales], DESC)  
 
member [Measures].[CityRank] as rank(([Geography].[Country].CurrentMember,[Geography].[City].currentmember),OrderedCities)
member [Measures].[CountryRank] as rank(([Geography].[Country].CurrentMember,[Geography].[City].[All]),OrderedCities)

member [Measures].[ProfitRunningTotal] as
sum( 
Topcount(OrderedCities-TopCount(OrderedCities, ([Measures].[CountryRank]))
,[Measures].[CityRank]-[Measures].[CountryRank])
,[Measures].[Profit]) 

select 
{[Measures].[Net Sales]
,[Measures].[CityRank]
,[Measures].[CountryRank]
,[Measures].[Profit]
,[Measures].[ProfitRunningTotal]
} on columns,

OrderedCities  on rows

from [MyCube]

What's happened to http://RichardLees.com.au/sites/demonstrations?

For the last 11 years I have operated a public business intelligence demonstrations on

• http://RichardLees.com.au/sites/demonstrations  and 
• http://EasternMining.com.au/sites/demonstrations

These sites are no longer available, I am afraid.   Last week I had a hardware failure and I don't have a budget for new hardware.  Hopefully, everyone is now clear how powerful and scalable Microsoft Analysis Services is and everyone is aware of the features in SQL Server data mining, Excel Services, PerformancePoint, close to real-time cubes with 1,000's of inserts/second etc.

There is a chance that someone might donate some hardware and I might put the demonstrations on again, but there is no plan at the moment.

Please don't hesitate to contact me should you have any questions regarding these demonstrations.

Vote for cubes in memory

As I have blogged before http://richardlees.blogspot.com.au/2011/12/why-doesnt-ssas-cache-entire-cube.html large multidimensional cubes are not retained in memory even though there is plenty of RAM and msmdsrv is using less than LowMemoryLimit.  Msmdsrv tends to hold about 2GB of aggregation cache, although this isn't a limit and it may be a result of the cache cleaner being too aggressive.   Whatever the reason, the problem is that msmdsrv won't hold data in cache even though there is plenty of RAM.   I have raised this as an issue with Microsoft, but I need your support to get this changed in the product.  Please go to https://connect.microsoft.com/SQLServer/feedback/details/760107/multidimensional-cube-is-not-retained-in-memory-even-though-there-is-plenty-of-ram  and vote for this enhancement.

This enhancement will mean that if you have a 20GB cube on a dedicated 100GB server, msmdsrv will retain the entire cube in memory and no further IO will be required.  As the product is now, with a high query load, the IO throughput can be measured in terabytes during a busy day.

By the way, in SQL Server 2012 we have Tabular models, which will store the entire cube in memory, but these models don't have the functionality that multidimensional cubes have, and they only work if the entire cube can fit in memory.

Thank you for helping make SQL Server a better product.

Cube processing goes slow when there are dimension key errors

I try and keep my cubes as clean as possible, so that dimension key errors are minimised. However, as a safeguard I tend to enable UnknownMembers. This way the totals are correct even if the customer, item, cashier etc isn't found in the dimension. 

Unfortunately, SSAS (2008 R2) still consumes quite a lot of cpu dealing with these UnknownMembers. This is fine if you only have a few million facts to process, but if you have billions (or even just hundreds of millions) processing noticeably slows down. This manifests as a single cpu busy in msmdsrv. The difference in cube processing time can be by a factor of 20, or so. My rule of thumb is that a partition should be able to process tens of millions of facts per minute. However, if there are key errors, this might drop to less than a million per minute.

If you have configured the ErrorConfiguration to "IgnoreError" you won't even see any error messages when you process in the foreground. But the cpu overhead is still there. The same goes for KeyErrorLimit, cpu will continue to be consumed even though it has stopped logging.

My suggestion is to try, as much as possible, to avoid dimension key errors. A simple coalesce(DimKey,-1) with a "unknown" key value (-1) as the last parameter value, combined with a "unknown" (-1) dimension member. Of course, you don't have to remove every dimension key error, just ensure that there aren't millions (or billions) of them.

My SSAS databases are corrupted

Did you happen to have a unplanned shutdown?  This is quite likely to be the cause of the failure.  This is unlike SQL Server, which has a transaction log and will always recover databases on restart.
As I have mentioned earlier, SQL Server Analysis Services exploits the Windows File Cache to help minimise IO, which might lead you to suspect the Windows cache.  However SSAS uses the FlushFileBuffers API to minimise the possibility of having dirty pages in the Windows File Cache.  However, this does not protect SSAS from corruption on an unplanned shutdown.
So, if you really want to avoid corrupted SSAS databases on power failure, you should ensure your SSAS servers are supported by UPS.
Most production SSAS servers have UPS.  But, if you don't have UPS, like my demonstration site (http://RichardLees.com.au/Sites/Demonstrations) and, like my site, the cubes are continually updating, when the power fails, SSAS databases have a good chance of being corrupted.  The only solution to this, that I know of, is to restore the affected (typically all) SSAS databases.  I find the quickest way to do this is to stop SSAS, empty the SSAS data directory, start SSAS and recover all the databases.  One thing you will also need to do is add back any userids that had SSAS administrator privileges as they were held in a file on the SSAS data directory.

Why doesn't SSAS cache the entire cube?

Would you expect a 36GB server dedicated to SSAS to eventually cache an entire 11GB cube in memory?  If so, you would be wrong.  SSAS does not necessarily keep aggregations in memory, even though memory used is less than Memory\LowMemoryLimit.

I have seen this effect on many production servers but never quite believed it.  There is much more RAM than cube size, yet SSAS is continually losing data from cache and asking for physical IO.  Actually, SSAS relies on the Windows File Cache to reduce much of its IO.  However, the Windows File Cache does not cache the entire cube either.

To demonstrate this effect, I have set up a 36GB server, dedicated to SSAS, and set up an 11GB MOLAP cube.  Then, over a period of 19 hours, I have client tasks querying the cube in a semi random fashion.  The MOLAP cube was not updated over the period.  Over the 19 hours, if SSAS or Windows File Cache were effective, there wouldn't be much more than 11GB of read IO on the SSAS data drive over the 19 hours.  However, the system reached an equilibrium, where it was requesting over 20MB of read IO/second.  If you add this up, the system read 1.3 terabytes of data from the 11GB cube (20MB*60*60*19).  Obviously, the system was not effectively caching the cube.  This is very different from SQL Server, which would quite happily keep an 11GB database in memory, effectively eliminating further read IO.

My server was running Windows 2008, SQL Server 2008 R2 with default configuration settings.  The SSAS database was located on the d: drive.  Nothing else is located on the d: drive
Here is a Perfmon chart with some of the key counters during my test.
SSAS (msmdsrv.exe) did not use more than 12GB of memory during the test, which is well below the LowMemoryLimit 65% (which is about 23GB).

During the 19 hours, SSAS requested, on average, 102MB/second of read IO.  The Windows File Cache was able to satisfy about 80% of these read requests, so the physical disk bytes/second was only 20MB/second. 

The Perfmon chart above shows some of the key counters.  Notice

• The Process (msmdsrv) Bytes/second y axis scale is in MB, often going off scale at 100MB/sec. These are not necessarily physical IO, as the Windows cache will satisfy many.
• The PhysicalDisk Bytes/second is averaging 20.1MB/second for the 19 hours
• The Windows Cache Bytes is sitting between 0.6GB and 1.1 GB.
• The red line (Cache Copy Reads/sec) is the IO requested of the cache.  These are IO that the Windows cache has managed to satisfy by finding the page in its cache.
• See how the Cache Copy Reads/sec is satisfying IO requested of the msmdsrv process, reducing the PhysicalDisk Bytes/sec.  Not so easy to see, as the reads are reads/sec, while the PhysicalDisk and Process are in bytes/sec, but you can see it.

Now that I understand this effect, I am much more appreciative of the Windows Cache.  This is very different from a SQL Server system, where the Windows Cache has little to do as SQL disables the Windows File Cache for its database files.

However, you can clearly see that SSAS (or the system) is performing much more IO than would be necessary if the entire 11GB cube was cached in memory.
There are a couple of takeaways from this exercise

1. Be thankful that we have a Windows Cache, and consider configuring it for SSAS.
2. Try and put SSAS databases on very fast (ideally solid state disks) storage devices.  If you can't avoid the IO, at least make it fast.
3. Partition large cubes.  This helps reduce logical IO requests and to the extent it bunches the popular data together it will probably help the Windows Cache hold the hot data more effectively.
4. Hopefully, in a future version, either SSAS or the Windows File Cache will cache more of the cube.

By the way, this effect is not always noticeable.  If your cube is less than 2-3GB, then it is likely to be well cached between SSAS and Windows File Cache.  Also, if you cube is much larger than the memory available to SSAS, then you would expect to see continual IO, and it is likely to be quite well optimised.  However, when you have a 64 bit server with a cube that is larger than 3GB but is comfortably less than the server memory, you might be surprised to see the volume of continual IO.

Please help get this fixed by voting on https://connect.microsoft.com/SQLServer/feedback/details/760107/multidimensional-cube-is-not-retained-in-memory-even-though-there-is-plenty-of-ram

Parallelised Queries are Selfish Queries

SQL Server Parallel Query Processing and MAXDOP

I am prompted into writing this post after reading the msdn article Understanding and Controlling Parallel Query Processing in SQL Server.  It is a very informative article.  I cannot disagree with anything in the article, but there is an elephant in the room.  The article dissregards IO performance bottlenecks.  It actually states that it will disregard IO performance.  "We assume that your I/O system is configured properly ..... so that the I/O system is not the primary performance limiter."  I am going to discuss IO performance with MAXDOP, because I believe it is often the main performance limiter.

As a bit of background, SQL Server has a parallel query processing feature that is enabled by default.  This permits SQL Optimiser to choose a plan that involves breaking one query into sub tasks that can run in parallel.  This can involve executing, essentially, the same plan, but in parallel subtasks, or it can involve a completely different plan and again breaking the query into subtasks that can run in parallel.  Really quite an amazing piece of technology. When it is working well for a query, it is interesting to see figures like cpu=10 seconds, elapsed=1 second!

In many of the very large database environments where I work, IO is the most critical resource.  Perhaps this is my misfortune, but I suggest that IO performance is often a performance limiter in large database systems, and probably will be so until SSD (solid state disks) are more commonly employed.  The problem with parallel query plans is that one query can simultaneously request multiple table scans, selfishly requesting more resources in parallel in the hope of getting a faster response.  Just one table scan from one query will tend to have parallel IO requests, as multiple SQL Server read engines are given multiple page read ahead requests.  So, even with a single non parallelised SQL query, there will already be some parallel IO requests.  Run several non parallelised queries at the same time and there are many more parallel IO requests than there are queries.  That is all before Parallel Query Processing kicks in!

Let's take a very simple example, where the SQL query has a predicate on a non indexed column of a very large table (the table is larger than SQL cache), therefore requiring a sequential scan of the table.  Without Parallel Query Processing, SQL Server will sequentially scan the very large table, using several read engines working in parallel to get multiple page ranges.  Note, there is already parallel IO requests with a single non parallelised SQL query.  Now if we enable Parallel Query, SQL can break the scan into several sub tasks, each of which will ask for range scans of the table.  Each of these scans can busy up multiple read engines, which can lead to IO throughput saturation.  That is the parallelised query will result in scanning the same table, but it will flood the IO system with many more parallel requests that can end up in a long queue.  A good way to think about it is to imagine an old fashioned bank branch with 30 tellers and everyone queues up for the next available teller.  That would be non parallelised.  Now imagine that someone comes in with bags of money that requires lots of counting and when he/she gets to the front of the queue, all the tellers attend to counting his/her money together (in parallel).  This might be good for the selfish individual, they get served faster, but it is not good for the performance of the branch, as now all customers will be waiting for the selfish customer to finish.  For this reason, I like to think of parallelised queries as selfish, or unsocial.  They want to take all the resources, even though this is not an efficient use of the overall resources and others are negatively impacted.  This is fine in a system with very few (perhaps one) customer.  Note, there is always an overhead in parallelising a query, even if it is using essentially the same plan as a non parallelised query.  This is because there is overhead in distributing and regathering the sub tasks.

There is an argument that data warehouses have very few users so that Parallel Query is an ideal performance option.  However, I see modern data warehouses with large numbers of users simultaneously submitting queries (dashboards, web pages, reports etc), and you typically don't want one user to selfishly hog critical resources such as IO.

I might sound like I am against Parallel Query.  That isn't true.  It is a fantastic feature.  However, I would encourage DBAs to consider setting Max Degree of Parallelism to 1.  Note; that is not disabling Parallel Query, it is just the default.  So, by default, queries will not be selfish.  If you have a particular ETL task or report query that you want to parallelise, you can simple add OPTION (MAXDOP n) to the query, where n is the number of potential sub tasks running in parallel.  So you are not turning it off, you are just ensuring that queries aren't selfish by default.  Also, just to be clear, with Max Degree of Parallelism set to 1, your system can still have many SQL queries running in parallel (with each other) it is only that individual queries will not be broken into parallel running subtasks.

The reason I configured Max Degree of Parallelism to 1 the first few times was because I was working in environments with a wayward parallelised queries.  SQL Optimiser chose a very very poor plan thinking that parallelisation would make this plan fast.  The same queries with Max Degree of Parallelism =1 (or MAXDOP 1) the optimiser chose a very different, and efficient plan.  When I reduced the "Max Degree of Parallelism" to 1, these queries, and the whole system ran more efficiently.

An annoying limitation of MAXDOP is that you are unable to set MAXDOP for queries that SQL Server Analysis Services uses when processing fact tables.  You can only provide SSAS with a table or view name, and it constructs the query.  There is no way to inject a MAXDOP option.  This is another reason I like to set Max Degree of Parallelism = 1, so that the default setting is non parallelised.

One word of warning.  Changing Max Degree of Parallelism to 1, can (in fact by design it should) make some queries run slower.  It can also change dramatically the query plan (as I discussed above) and while this will usually result in more efficient queries, it can also involve a poorer performing plan.  In my experience the reverse is more likely (when there are considerably different plans between parallelised and non parallelised, the non parallelised plan is more often the more efficient.  Consequently, it is less risky to configure Max Degree of Parallelism =1 while developing a solution (and using MAXDOP n for individual queries where appropriate) than to change a production environment.

The effect of Max Degree of Parallelism is not just on the individual query.  If you want to know the impact of Max Degree of Parallelism, you should monitor vital statistics (cpu, memory, IO), get average query response times (for common queries) and compare them with before and after  configuration changes.  Another, albeit small, cost of Max Degree of Parallelism<>1 is that each parallelised query will now have two plans (it always keeps a non parallel version for when cpu is high) so that more pressure is put on the plan cache (it is bigger, or it holds fewer plans).  Note, SQL Server is great in that you can change the Max Degree of Parallelism option dynamically.  No SQL Server restart required.  You might even consider setting Max Degree of Parallelism =1 for the online day and resetting Max Degree of Parallelism =0 for the overnight ETL.

A closely related issue is Resource Governor, which also has a MAXDOP option that looks like it can override the MAXDOP option in the query.  However, as I understand it, the Resource Governor does not work like MAXDOP in a query or sp_configure.  If you set MAXDOP to 1 in the Resource Governor, the underlying queries can still chose parallel plans (no change there).  What the Resource Governor does is to restrict the query to only one thread!   This is almost the worst of both worlds, as the optimiser can choose the selfish (potentially faster) parallel plan, but the Resource Governor will ensure that the parallel tasks are serialised with just one thread.

MDX Root() function doesn't support multiselect

One of my other favourite MDX functions is Root(). It's a really easy way to get all the filters off a dimension. I often use it in calculations defined in the cube.

However the Root() function does not support multi-select and you might get this error message.

#Error Query (6, 30) The MDX function ROOT failed because the coordinate for the 'Region' attribute contains a set.
Fortunately, there is a workaround. Unfortunately, it means not using the Root() function and going back to the .[All] member of all the attributes (or all the ones being used in the query) of the dimension.
For example, the following query will hit the error.
WITH
MEMBER [Client Host].Region.[AfricaAsia] AS
AGGREGATE({[Client Host].Region.&[Africa]
,[Client Host].Region.&[Asia]})
MEMBER Measures.[HitsPercent] as
[Measures].[Request Count]/(ROOT([Client Host]), [Measures].[Request Count])
SELECT
{Measures.[HitsPercent]}
ON COLUMNS
,[Date].[Month].[Month]
ON ROWS
FROM [EasternMining]
WHERE
([Client Host].Region.[AfricaAsia])

The fix is simply to change Root([Client Host]) to [Client Host].Region.[All]. ie.
WITH
MEMBER [Client Host].Region.[AfricaAsia] AS
AGGREGATE({[Client Host].Region.&[Africa]
,[Client Host].Region.&[Asia]})
MEMBER Measures.[HitsPercent] as
// [Measures].[Request Count]/(ROOT([Client Host]), [Measures].[Request Count])
[Measures].[Request Count]/([Client Host].Region.[All], [Measures].[Request Count])
,format_string="#,#0.00%"
,non_empty_behavior="measures.[Request Count]"
SELECT
{Measures.[HitsPercent]}
ON COLUMNS
,[Date].[Month].[Month]
ON ROWS
FROM [EasternMining]
WHERE
([Client Host].Region.[AfricaAsia])

Note, I have only put the .[All] member in for the Region attribute, whereas you really need to add in a .[All] member for all the dimension attributes to be equivalent to the Root() function.

By the way, I don't know why the Root() function doesn't support multi-select. One would imagine that it should.

Because of this limitation, I might be using the Root() function less liberally in calculations stored in the cube.

SSAS Dynamic Security

Imagine the scenario where there are hundreds or thousands of cube users, most of which are only permitted to see "their" data. And there is a dimension with an attribute containing their Windows userid. It would be very tedious to set up a security role for every individual. Not to mention the maintenance in adding and deleting roles. Although this could be somewhat automated with AMO scripting.

A technique I like to employ is to set up just one SSAS role, which essentially contains everyone that might have some access to the cube (integrated security only). So, in the Dimension Data security tab, I restrict the "Allowed member set" to just those members where the member name is equal to the username(). Username() is an mdx function that returns the Windows userid.

The Allowed member set might look something like

{StrToMember("Branch.BranchManager.&[" + UserName() + "]")}

The default member could be

StrToMember("Branch.BranchManager.&[" + UserName() + "]")

You can add other roles, for example, one with read access to the entire cube. So the users that can only see their Branch, would be in the first role (they would only be able to see their own branch) and head office users in the second role would be able to see the entire cube.

This is a very elegant solution. As you can see, it requires no maintenance, as new/old users will automatically be able to see their own data. If the branch manager changes, the old user will have no read access and the new user will have read access to that branch.

There are many ways to extend this security. I have combined it with application tables that list users' access. This can be a many-to-one or many-to many relationship. No problem, just create a dimension (possibly many-to-many) , and grant read access to the dimension in the same way. Also, it might be that you only want part of the username, or that you want/don't want the domain. Again, that is no problem, you can use VBA functions in the MDX expression to get the substring that you want. You could also use this technique to manage a black list (Denied member set) instead of a white list (Allowed member set).

A classic use of this technique would be to support sales persons to see information relating to their customers. This technique might be combined with other restrictions. For example the dynamic security role might let browsers see a subset of their data, such as quantities, but not dollar values. And only for their customers.

Note, there is an overhead in Dimension Data security, but in my experience it is almost undetectable. Of more concern, if you needed to use it, would be the many-to-many dimension. Be wary of performance if your m2m dimension and/or m2m fact table is large.

Also note, you probably want to check the "Enable Visual Totals" check box, so that users can only see the total for members they are allowed to see.

The use of this dynamic security technique can be used in conjunction with conventional roles in the same cube.

Querying Analysis Services For Cube Metadata

SSAS DMVs can be very useful in providing metadata information. Ie.

$SYSTEM.MDSCHEMA_CUBES
$SYSTEM.MDSCHEMA_DIMENSIONS
$SYSTEM.MDSCHEMA_FUNCTIONS
$SYSTEM.MDSCHEMA_HIERARCHIES
$SYSTEM.MDSCHEMA_INPUT_DATASOURCES
$SYSTEM.MDSCHEMA_KPIS
$SYSTEM.MDSCHEMA_LEVELS
$SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
$SYSTEM.MDSCHEMA_MEASUREGROUPS
$SYSTEM.MDSCHEMA_MEASURES
$SYSTEM.MDSCHEMA_MEMBERS
$SYSTEM.MDSCHEMA_PROPERTIES
$SYSTEM.MDSCHEMA_SETS

SQL Server Enterprise Manager's Query editor supports "Select * From " these view. Interestingly, a lot of this information is also available through MDX. For example, here is an easy way of getting a list of all attributes (strictly speaking, hierarchies) and their level names.

WITH
member measures.HierarchyCount as dimensions.count
set HierarchySet as tail([Date].[Year Month Day].[Day],measures.HierarchyCount)
member measures.MyHierarchyNo as rank([Date].[Year Month Day],HierarchySet)-1
member measures.DimensionName as
left(dimensions(measures.MyHierarchyNo).dimension.unique_name,
instr(dimensions(measures.MyHierarchyNo).dimension.unique_name,".")-1)
member measures.MyHierarchy as dimensions(measures.MyHierarchyNo).name
member measures.HierarchyLevels as dimensions(measures.MyHierarchyNo).levels.count
member Measures.Level1Name as
case when Measures.HierarchyLevels>1 then dimensions(measures.MyHierarchyNo).levels(1).name else null end
member Measures.Level2Name as
case when Measures.HierarchyLevels>2 then dimensions(measures.MyHierarchyNo).levels(2).name else null end
member Measures.Level3Name as
case when Measures.HierarchyLevels>3 then dimensions(measures.MyHierarchyNo).levels(3).name else null end
member Measures.Level4Name as
case when Measures.HierarchyLevels>4 then dimensions(measures.MyHierarchyNo).levels(4).name else null end
member Measures.Level5Name as
case when Measures.HierarchyLevels>5 then dimensions(measures.MyHierarchyNo).levels(5).name else null end
member Measures.Level6Name as
case when Measures.HierarchyLevels>6 then dimensions(measures.MyHierarchyNo).levels(6).name else null end
member Measures.Level7Name as
case when Measures.HierarchyLevels>7 then dimensions(measures.MyHierarchyNo).levels(7).name else null end
member Measures.Level8Name as
case when Measures.HierarchyLevels>8 then dimensions(measures.MyHierarchyNo).levels(8).name else null end
SELECT NON EMPTY
{measures.DimensionName
,Measures.MyHierarchy
,Measures.HierarchyLevels
,Measures.Level1Name
,Measures.Level2Name
,Measures.Level3Name
,Measures.Level4Name
,Measures.Level5Name
,Measures.Level6Name
,Measures.Level7Name
,Measures.Level8Name
} ON Columns,
HierarchySet on Rows
FROM EasternMining

Just replace the Date hierarchy name with a hierarchy from your own cube. This hierarchy is used to list out the attributes, so just ensure there are at least a few hundred members. These members are used to drive the query.

Not sure when this would be useful. Perhaps if you only had browser access to a cube, it did not support DMVs, and you needed to see hidden attributes.

What's preventing Excel from being the ubiquitous cube browser

I have said earlier that Microsoft’s Excel 2007 is not the ubiquitous cube browser that it should be. What’s wrong with it? Well nothing is really wrong with it, but there are several features, that I would expect from a product that is 10 years old. With OLAP services first release 1998, there was an update for Excel PivotTable. As I understand it that first version was largely the result of one great developer (TC). However, since that release, the upgrades have been unimpressive. Excel 2007 is a great improvement on Excel 2003, but it still has a long way to catch up to the sophistication that is available in Analysis Services. Here are a few of the things I would like to see in Excel

• Full support for calculated members. They are partially supported, calculated measures are OK, but other calculated members are treated as second class citizens.


• Ability to create calculated members that are stored within Excel, and available like any other calculated member for that user.


• Support for 3 dimensional queries. Currently, a user can only ask for a two dimensional report. Excel, is inherently a 3 dimensional space when you include worksheets. So I would like to be able to create a PivotTable with a dimension placed on the worksheet axis.


• Support for naive users who open up a dimension member list with a million members. For example, ask them if they really want the one million rows (columns) and perhaps defaulting to a top 100 list.


• More optimised MDX. Sometimes I despair with the performance of pivottable queries, and when I look at the MDX I see that it could run quickly with more efficient MDX.


• Ability to see properties of ancestors


• Better integration into Excel spreadsheets. For example, ability to format measures and PivotTable to remember these, even after drill down, nesting etc.


• Ability to define sets.


• Eposure to some set functionality in a gui fashion. For example, it might allow the user to select a crossjoin on rows with an exception function to exclude some members based on a filter criteria.


• Ability to right click on members to “select only” or “deselect”


• More control over charts. For example mixing the dimensionality of measures so that one measure is cumulative on series and the other is unrelated to the series.

Don’t get me wrong, even with this wish-list, I am a great fan of Excel PivotTable. As a rich client cube browser, it is still my favourite. It’s just that it isn’t keeping up to Analysis Services, which is truly a market leader in the OLAP field. In a later blog, I will write about my favourite features of Excel pivottable, many of which are not immediately obvious.

For real-time OLAP, Data Mining, Excel Services and related demonstrations goto http://RichardLees.com.au/Sites/Demonstrations

Analysis Services Database Name and DatabaseID

Typically an Analysis Services database has a DatabaseID the same as the database name. However, if you rename a database, the DatabaseID will stay the same. This can be a source of confusion and it can lead to useful operational techniques.

Whenever anyone connects to an Analysis Services database, they do so by selecting a particular database name. So when you rename a database, users will see and use the new name. However, SSIS packages that perform Analysis Services processing tasks select the database by the DatabaseID. Also, Analysis Services keeps the DatabaseID as a unique identifier. So when you rename an Analysis Services database, existing SSIS packages will be looking for the old DatabaseID.

So if you have an Analysis Services database, say EasternMining, and you rename it to Eastern Mining, then users will only connect to it by asking for Eastern Mining, which is fine. However if you try to restore a database called EasternMining (which doesn't exist anymore) it will fail, even though you have enabled Allow Database Overwrite. The message will be something like Backup and restore errors: An object with the same 'databaseid' ID but different 'databasename' Name already exists on the instance. (The AllowOverwrite flag only applies to the Object ID, not to the object name.) I find the message a bit misleading, but what it is saying is that a database with that DatabaseID exists already.

Unfortunately, you cannot change the databaseID, without doing something like backing up and restoring the database. Interestingly, an xmla create database statement will overwrite an existing database and DatabaseID. If you want to know the DatabaseID for an Analysis Services database, just right click on the database and select properties.

I have previously blogged on how you might use this to maintain close to continuous availability when you need to deploy a new Analysis Services cube structure. http://richardlees.blogspot.com/2009/05/new-analysis-services-database-version.html

Space Considerations for Various OLAP Data Types

Data types can have huge space and performance implications on large SQL tables, and there are similar implications for large OLAP cubes. We all know that double word numbers take up more space than single word numbers, but what exactly is the difference?

I have performed a simple benchmark to compare the space used of the various data types. My tests involved a

• 44 Million row fact table
• 3 measures containing values between -99 and 1039.22
• 5 dimensions
• SQL Server 2008
• Zero aggregations

I used 6 datatypes in SQL Server tables. The SQL data types I employed were

• Money
• Float
• Decimal
• Bigint
• Integer
• Real

For the Money data type, I created two cubes, since Analysis Services defaulted the Money data type to Double. I created one cube with the default Double data type, and another with the cube data type as Currency.

The results show that the best performing (in terms of size) were

• Bigint and Integer (41% smaller than Double)
• Currency (25% smaller than Double)
• Single (3% smaller than Double)
• Double

Note, Sometimes the data type you choose is determined (or constrained) by the domain of the numbers you are aggregating. For example, if your numbers contain significant digits after the decimal point, then Bigint and Integer are inappropriate, unless you are happy to round them. Likewise, for imprecise numbers, Double gives you the most precision, which you might need. But just because the underlying SQL table uses a single or double byte precision data type, if the number is always an integer, you might as well tell Analysis Services to use a Bigint and save on space.

It shouldn't surprise you that the binary numbers (Bigint and Integer) are the smallest on disk, nor that they took the same space as each other. Analysis Services compresses all the cells before writing to disk, and the binary numbers will tend to have lots of leading zeroes, which compress very well. Similarly, but to a lessor extent, Currency (really a packed decimal) compresses next best.

Just in case you are wondering why bother fiddling with data types when the cube is only a gigabyte or two in size, and disk space is plentiful. The reason is that if you can save 40% in your cube size, processing and querying will be noticeably faster. With a smaller cube, your cache will warm faster, and if the entire cube doesn't fit in RAM, you will have a greator proportion of your cube in RAM with a smaller cube. Ie. your queries will run much faster.

Richard

for real-time online OLAP demonstrations go to http://RichardLees.com.au/Sites/Demonstrations