Calvin Fern's Blog

SSAS Tabular, PowerPivot and conformed snowflakes

2013-03-19T22:38:25Z

Many of you will be aware of the Kimball concept of conformed dimensions. This is the principle of joining multiple fact tables, often from multiple source systems, into the same dimension table. Using this shared dimension the many fact tables can be analysed by the single view of the dimensional data and deliver powerful cross dataset analysis.

In many cases the granularity of the differing source systems is different, a typical example of this is shown in the Adventureworks where sales data is gathered against an individual product but survey data is gathered against Product Category and SubCategory. In order to facilitate easy key generation and lookups the Product Category and SubCategory are then separated from the main product table, this is described as a Snowflake design pattern as shown below.

The first SSAS Tabular consideration here is that if you need to join in at a less granular level than the primary dimension table is is essential to present a snowflake to SSAS tabular, it cannot join in at a higher grain like SSAS Multidimensional can. This is due to its need for a distinct list on the dimension side of the join key.

The relationships in SSAS Tabular or Powerpivot can then be set up as shown below to join other data in at a higher grain such as the FactSurveyResponse below

The next step is to browse this data and check that we can indeed view all our data by Product SubCategory, using the ‘Analyze in Excel’ button we can browse the development cube and set up the pivot table below. Allowing the slicing of disparate data by common dimensions in this fashion is one of the biggest business benefits of data warehousing.

The solution thus far works but, in Excel and other add hoc tools, the three product tables and large number of attributes are confusing to the end user. As such we should look at hiding unused and unwanted columns and using the tabular cube to denormalise or ‘flatten’ the options presented to the business user. Unwanted columns are removed by using the hide from client tools options or removing unwanted columns from the cube all together (ideally using SQL Views). To reduce the number of tables presented to the user we will hide DimProductCategory and DimProductSubCategory entirely from the user while adding derived columns holding this information to DimProduct. To achieve this we use the RELATED keyword in DAX to add a few new columns. Adding a ProductSubCategory Column to DimProduct we use the following expression:

=RELATED(DimProductSubcategory[EnglishProductSubcategoryName])

Once the above column is available, refreshing the Pivot table used earlier with the new column gives in the following result:

This incorrect result is the impact of the fact that relationships are only propagated towards the fact table automatically in Tabular and Powerpivot. We need to explicitly specify that the measure we are using relates to the DimProduct table through ProductSubCategory and the context of this table needs to be used. To do this we need to alter our measure calculation to take into account the related table DimProduct, unsurprisingly this is achieved with the RELATEDTABLE function. We use the CALCULATE function in combination to aggregate our data as appropriate. The measure DAX formula can then be defined as follows:

=CALCULATE(
    DISTINCTCOUNT([CustomerKey]),
        RELATEDTABLE(DimProduct)
        ,VALUES(DimDate),
        VALUES(DimCustomer)
    )

A values statement is needed to allow the current filter context to be used for all other dimensions on the fact, this doesn’t seem very elegant and I am open to a better way of doing it. The measure as defined above can then be used in excel to give the same result as we first achieved while giving a much cleaner user interface with a single product table for users to find all their information.

Any question or improvements get in touch here or @CalvinFerns

Thanks for reading

Technorati Tags: SSAS,Tabular,PowerPivot,Snowflake,Kimball,Comformed,Dimensions,Granularity

SQL PASS Summit 2012

2012-11-13T05:11:49Z

Having just returned from SQL PASS I thought I would blog a few highlights about the conference and some of the things I came across and am looking forward to. I went over to Seattle with two of my colleagues and we started with a little sight-seeing to get our body clocks back in sync and I would thoroughly recommend it. The jetlag from the UK gets to you for the first couple of days and you get to see things like this:

The Adatis SQLPass team: Myself, Neil Dobner, Tim Kent

This was my first time at PASS and I was not disappointed. The first couple of evenings in Seattle were spent catching up with some people I have met at SQL Bits. The SQL community being what it is I was quickly introduced to more people and making new connections. The #SQLfamily on twitter are a welcoming bunch and really add a lot to the atmosphere of a friendly conference. I would strongly recommend using twitter for both connecting with people and using the #SQLHelp hash tag to ask questions. I had come across a number of the speakers at SQL Bits and they were all very good quality and I heard very little negative comment on the sessions across the 3 days.

There were a good number of highlights for me that I would like to pass on in case they are as relevant to you as they are to me. I've split them down into a few areas, firstly Powerview:

PowerView coming to SSAS Multidimensional
PowerView in Excel
Powerview on Multidimensional will come to Sharepoint well before it gets into the Office Excel release cycle
Image Urls and geospatial reports are also particularly easy

Some brilliant stuff in a Tabular internals stuff given by Akshai Mirchandani and Allan Folting, DAX queries by Alberto Ferrari and 2012 SSAS by Chris Webb:

Insights into value encoding and hash encoding compression which makes sense of guidance such as
Don't bring in any row level identifier or document numbers etc unless you really have to
Use a process defrag as maintenance when doing updates and deletes
Lower end hardware can be quicker, ie go for less but faster procs
Profile you DAX queries to look out for formula engine calls using: CallBackDataId
Partitions are not eliminated by the query optimiser they only help you manage processing, this is particularly impressive because the engine is so fast it didn't need this!
Direct query really is unusable given the limitations and the awful SQL it generates
Two thirds of the SSAS multidimensional codebase is there to deal with M2M correctly

Finally some database engine stuff:

Windowing functions in 2012 really will be a lifesaver for complex queries, especially lag and lead for those moments where your user thinks you work in Excel
Updatable columns store indexes!
Licencing can be a nightmare of Cores, processors and virtualisation but the key way to keep it sensible on the new model is to keep you core density at 4 cores per processor.

I met loads of fantastic interesting people and have picked up some great contacts. The conference has also had the added side affect of getting me excited about SQL Server again. It is easy to get caught in the day job and forget what a brilliant product we work with and what hard work goes on at Microsoft to keep it that way.

Thanks everyone

See you Next Year? or maybe SQL Bits a bit closer to home.

@CalvinFerns

HP Business Decision Appliance–PowerPivot in a box!

2011-10-27T08:32:31Z

Further to my blog post about SQL server appliances Microsoft and HP also offer the very exciting Business Decision Appliance. This contains a preconfigured environment with:

Microsoft Windows Server 2008 R2 Enterprise Edition

Microsoft SQL Server 2008 R2 Enterprise Edition with PowerPivot integration for SharePoint

Microsoft SharePoint 2010 Enterprise Edition Prerequisites for SharePoint and PowerPivot

Appliance Administration Console

Appliance-specific SharePoint Home Page

Up to 80 Concurrent Users

The aim of this appliance is to provide a safe and scalable environment for business users to quickly put a PowerPivot environment in place. This is isolated from existing systems and can be implemented by a business department with very limited IT involvement. Its isolated nature is important as many organisations don’t run SharePoint 2010 throughout their enterprise. PowerPivot authors also need Excel 2010 but once reports are built and deployed they can be shared throughout the business through SharePoint using any web browser. Anyone who has already tried to set up an integrated SharePoint and PowerPivot environment will know that the installation is not simple. This appliance takes that pain away with a one click installation from first start up that can have you up and running in under an hour.

The key point is that business users love PowerPivot and its ability to quickly use their data to answer any question. They can respond dynamically, collaborate and share insights throughout the organisation. Importantly PowerPivot allows the creation of reports that look great and are very fast. All this is done from within the familiar Excel interface and requiring little training to get started. Any team of business analysts would likely have a massive boost in productivity from the installation of the BDA in their department. IT can then monitor those reports that are heavily used and decide whether they need making more robust through transition to enterprise software such as Analysis Services. PowerPivot use is at its easiest in an environment where there is a clean data warehouse but where the business aren't happy with the speed or responsiveness to change of their current front end. In an environment with more disparate and dirty data the end users need to be more SQL and data modelling skilled, but the payback can be even greater.

I see massive opportunity in this appliance for any team of data analysts to be able to deliver massive value to their business right now.

More Info on the BDA

SQL Server can scale

2011-09-21T17:52:17Z

If you work in large enterprises you often come across the attitude that Microsoft SQL Server can’t scale. People therefore turn to vendors such as Oracle and Teradata for solutions at very high cost. This attitude often comes from in-house experience of a growing SQL database which hits something between 5 and 10TB and starts slowing down and becoming difficult to work with. These databases are often run on multi-instance servers with shared SAN access and little thought to data fragmentation. Its no wonder that their environment has problems!

Microsoft Strategy

I was recently invited to a Microsoft partners event which aims to challenge these attitudes and give partners better information to discuss large scale SQL implementations. Their strategy is to use partnership with HP to give SQL server a balanced hardware configuration to run on. This will solve the major bottleneck which gives SQL Server the performance problems people perceive are with the software. They have created a range of solutions together to allow SQL server to scale effectively to meet any need. The first of these solutions was released a couple of years ago and there are more in the pipeline. There are real world case studies available from Microsoft detailing the success of this strategy for many early adopters. These solutions are targeted at the workloads they need to run and are detailed below.

Warehousing

< 5TB – HP Business Data Warehouse Appliance

20TB – 80TB – Fast Track Data Warehouse

126TB – 500TB – HP Enterprise Data Warehouse Appliance

Applications/ Consolidation/Private Cloud

100VM’s- 10,000VM’s

OLTP

On the way shortly - Will scale to the worlds largest OLTP Implementations

These solutions are all positioned such that they are going to be significantly cheaper than almost any other vendor while providing equal or better performance. The appliances also provide excellent improvements in time to market, drastically reducing required configuration time. I would also recommend the 5TB box to anyone building a data warehouse of any size. This give you the confidence that you are buying a competitively priced server which is optimised for a SQL workload and you are not going to hit any hardware bottlenecks which you might come across on a shared or custom build server. The evolution of the Microsoft appliance and fast-track structure has also produced predictable benchmarks. You can choose the performance you need by picking the right server and if you follow the best practices you can be sure that you will achieve the throughput and response time needed.

Oracle’s Exadata platform claims to be able to manage any workload at any scale. What they don’t specify in the sales briefs is the amount of configuration required to match the platform to the workload in your environment. This means you need to invest in a lot of time for your Oracle DBA’s to tune and configure the hardware correctly. It is then not a comparable product to a SQL server installed on a spare bit of hardware which has very little time invested in it.

With a balanced hardware configuration and best practice ETL Microsoft SQL Server can meet any scale requirement asked of it. So next time you come across the “SQL can’t scale” attitude don’t be afraid to let people know that they are out of date and SQL will be able to meet their requirement at a far lower cost than the competitors.

(There is another appliance out for self service BI but it deserves a post all of its own - BDA )

Slides from Avon Information management talk

2011-07-21T11:42:43Z

Distributing the slides from my talk last night, it was well received and I think everyone was able to take something away.

Credit goes to Marco Russo and Alberto Ferrari for their data modelling ideas, visit www.sqlbi.com

http://dl.dropbox.com/u/25925341/Learning%27s%20from%20large%20scale%20dw%20project.pptx

Hopefully not my last talk as i enjoyed doing it. Look out for further Bath based events at:

http://avonim.wordpress.com/

All comments welcome

Calvin

SSRS Language parameter using SSAS Translations

2011-07-05T09:52:24Z

Problem

SSAS translations are known to deal with warehousing language requirements well. We recently had a requirement for a multi-language report suite on top of a user browsed cube. First thought was SSAS translations, these will pick up the users local language settings and allow an elegant solution to the problem.

However a requirement was put forward for a language picker in the SSRS report suite to over-ride the users locale if they so desire. This was not so obvious.

Data source

We started with a list of languages to build a picker from, this was built by importing the table available here to a SQL Server and building it as a dataset in our report:

http://msdn.microsoft.com/en-us/library/0h88fahh.aspx

It is important to bring through the decimal value in column 4 and the short string in column 2 as we will see later.

Parameter Build

Use the dataset on MSDN to build a language select parameter. Use the language name in the label field and the decimal locale identifier in the value field. The setup should look something like this:

Default value setup

If you just need English as the default use the UK code 2057 as your default value, however a more elegant final solution is a little more complex.

Reporting services provides the built in variable User!Language which holds the users language in the short string ‘en-gb’ style format. So although we can access the users language it needs translating into a localeId before we can pass it to SSAS. Fortunately for us the same table we used above can be used to translate between short string and locale.

I achieved this using a further hidden parameter defaulted to the built in User!Language variable, the parameter was set up as detailed below:

Using the dataset where LanguageCode = short string in our source table

If this parameter is run first (it must be earlier in the report parameters list) it will contain the users language in the form ‘en-gb’ in the value and ‘2057’ in the label field. It is then simple to set the default of the first dropdown parameter to the value of the hidden parameter’s label using the following syntax.

=Parameters!UserLanguage.Label

I am open to suggestions of a way to get the default without a hidden parameter or a large switch statement so if you find something more elegant please let me know! It is also worth noting that if a user has a language that your source table does not have an entry for you are obviously going to lose the defaulting behaviour.

Parameter Ordering

It is essential to make sure that the parameters are ordered in your parameter list in the sequence they need to be evaluated in. In this case the hidden user language parameter needs to be first, followed by the visible language drop down parameter. Only then should other report parameters be added.

Passing Parameter value to SSAS

In order for SSAS to provide us the data in the language requested we need to pass the locale to analysis services. We can do this by over-riding the locale in the connection string. Go to your report data source and double click for properties. Then we need to edit the connection string expression as circled.

Here we need to make sure our connection to SSAS is provided with the locale from the parameter. We use the following expression to build our connections string.

="Data Source=localhost;Initial Catalog=" & CHR(34) & "Adventure Works DW 2008R2" & CHR(34) & ";Locale Identifier=" & Parameters!Language.Value

(CHR(34) is the “ character that we wanted to escape properly.)

This builds the string detailed below for the parameter selection Spanish.

=Data Source=localhost;Initial Catalog="Adventure Works DW 2008R2";Locale Identifier=3082

This should now preview and allow you to select the language and your data sets will return any translations you have present in the cube.

Notes

If you are using a shared data source reference this is not going to work for you as it is only possible to expression a data source held in the report. In this case perhaps you can use a solution along the lines of the one detailed by Mosha here: http://sqlblog.com/blogs/mosha/archive/2006/10/07/member-caption-translations-in-mdx.aspx

Another warning is to finish your report design first because you cant refresh datasets and metadata once the expression is set

Tested on 2008R2 only.

Hope this helps everyone and allows you to select your existing translations in a flexible way through SSRS. Your comments are welcome.

Calvin Ferns joins Adatis Blog team

2008-11-24T18:20:21Z

I joined Adatis in early August and am now going to begin blogging. My initial posts will be on SSRS and PerformancePoint, as my skills grow I’m sure the technicality and scope of my postings will increase.

Hope that I find some topics that are of use to you and look forward to your comments.