Adatis BI Blogs

Power View in SQL Server 2012 RTM

Jeremy Kashel — Wed, 28 Mar 2012 13:13:01 GMT

Power View has been given a couple of small changes from RC0 to the RTM release:

Firstly, you can now choose the colour of the various visualisations via the Themes section, which you’ll find on the new Styles ribbon tab:

This will allow you to change the colour for a slicer, table or card, whereas for a chart it will set the palette that gets used for the chart series. Surprisingly it seems to set the theme globally, rather than allowing you to set the colour/palette for a single object:

The other change that I’ve noticed is that the Card visualisation now has a Style option on the ribbon:

This allows the cards to be displayed without normal coloured background, with a much larger font. So potentially useful for highlighting key numeric facts:

I do really like Power View, in particular the clean look & feel, fast performance, export to PowerPoint and animations to name a few. However, even though RC0 added a lot of new features, I think it’s still missing some of the functionality offered by other reporting tools. I know that it’s marketed as a data visualisation tool rather than a reporting tool, but those lines will become a bit blurred for users. This is where Connect comes in…

One connect suggestion that I’ve created is to allow drill down in Power View charts. If you’ve used Power View to perhaps spot a trend or spike in the data, then it makes sense for you to manipulate the view to find the root cause, which I think drill down would help with. Other suggestions that I think would be useful are heat maps and allowing Power View to access multidimensional models. Please vote on Connect if you agree!

HTML Links Embedded within Text (SSRS 2008R2)

PhilKibaki — Fri, 23 Mar 2012 16:59:47 GMT

Overview

So, old news, from SSRS 2008 onwards, Reporting Services can render HTML Tags, although there are some limitations to the tags that can be used:

Hyperlink tag: <A href>
Text formatting tags: <S>,, ,
List handling tags: <OL>, <LI>,<UL>
Font tag:
Header, style and block tags: ,<H{n}>,<LI>, <DIV>, , <HN>

I’ll be talking about using the hyperlink tags to drill through or link back to the same report within a lump of text return by a dataset.

Say your dataset returns some summary text that describes the data in the report, like “Sales for the Accessories Category amounted to £#,###.## putting it at around the 11th percentile” and you would like to drill through on the Keyword “Category” only and not the whole text.

So you want

Sales for the Accessories Category amounted to £#,###.## putting it at around the 11th percentile

Instead of

Sales for the Accessories Category amounted to £#,###.## putting it at around the 11th percentile

From a lump of text returned by a dataset.

Solution

We can handle this kind of request by generating the hyperlink to the report you would like to drill through in the dataset. This is as opposed to using textbox actions in the report. There are a number of advantages of handling this in the dataset

Turn on the HTML markup type in the place holder properties for the Summary_Text Field.

HTML Markup Type Setting

Now, you need to build the link to the report you are drilling to, this is best done in the report to keep it dynamic, therefore, deploying the report to a different server or folder won’t mean you have to change any code as you are going to use the global parameters available in SSRS.

Create a hidden parameter that will hold the expression below. Alternatively, you can have this expression directly in the dataset parameter, just create a dataset parameter and in the value expression add this hyperlink expression.

Pass this in to your dataset to complete it with the missing parameters; in this case Category Name is missing.

HTML Link Expression

HTML Expression in Dataset Parameters

The incomplete generated link being passed to the dataset will look something like this:

In your dataset, complete your link with the missing parameter.

Complete your HTML Expression and Create Hyperlink

It may be worth pointing out at this stage you can also add other HTML tags to edit the look of the text. You can add bolding to the text – as in my example above, or even have some conditional formatting to colour code the text. For example green for high percentiles etc. (see the list of HTML tags available at the beginning of this post).

For this example, I am just linking back into the same report and passing in the category that has been clicked. You can of course link to a different report/Drill through.
You end up with something like this

How it will look

You can find the example RDL here. I used the AdventureWorksDW2008R2 Database.

DQS Matching Vs SSIS Fuzzy Grouping and Lookup

Jeremy Kashel — Fri, 23 Mar 2012 11:06:59 GMT

When the term fuzzy matching comes up in the Microsoft world, it’s natural for anyone who’s used the BI stack to think of SQL Server Integration Services (SSIS), due to the fact that it has both Fuzzy Grouping and Fuzzy Matching components. Therefore, when using the matching in Data Quality Services (DQS) the other day, I thought it might be worth contrasting the matching capabilities in the two products.

Overview

The SSIS Fuzzy Grouping transformation is typically used on a set of data containing duplicates, as it will operate across all of the rows, grouping similar rows together based on fuzzy comparisons, with a view to eliminating duplicates. In contrast, the Fuzzy Lookup transformation takes a value in the SSIS pipeline and uses fuzzy matching to match the input value against a set of clean reference data in a database.

The matching in DQS takes a set of data and groups the data into clusters, based on a number of rules and other factors, again with a view to removing duplicates. It’s therefore only directly comparable against the SSIS Fuzzy Grouping, rather than the Fuzzy Lookup.

Test Scenario

For the purposes of this blog post, I’ve got a sample query from AdventureWorksDW, taking data from the DimCustomer table. I’ve unioned all that data with a few sample records that contain typos. E.g. I’ve got 'Pamela Fernndez' misspelt for one customer and '4610 Pinto Rd' instead of ‘Road’ for another. I’m going to attempt to de-duplicate data in SSIS first and then do the same thing in DQS to see if there is any difference.

SSIS Fuzzy Grouping

As shown below, I’ve just got a very simple OLE DB source passing some data into a Fuzzy Grouping, then I’m going to examine the data via a data viewer:

The columns that I’m passing into the Fuzzy Grouping for the Customers are Full Name, Address Line 1, Gender and City just for the purposes of this blog post. I’ve configured the Fuzzy Grouping transform to carry out Fuzzy Matching on Address Line 1 and Full Name, as these are the most inconsistent (intentionally) across my made up data. I’ve also dropped the threshold right down to 0.40 to see a full range of matches that we may or may not get:

I’ve filtered the data for customers with a surname of Fernandez or a first name of Pamela, which includes two made up records. When I run the package, the data viewer produces the following results:

The _score column has a high score of 0.8245935, which is based on the similarity scores of FullName and AddressLine1. There are 3 records included in the cluster, including someone called ‘P Fernandez’ who is male. Although that’s been matched, it’s only because I decreased the threshold, plus it can easily be excluded by adding an exact match for Gender, as shown below:

DQS Matching

So overall SSIS copes well with the test scenario, how does DQS compare?

After creating a knowledge base, creating domains and carrying out knowledge discovery, it’s possible to use DQS to create a Matching Policy. The Matching Policy involves building a number of rules that determine how the data matches. The starting point in creating a matching rule is determining which domains you want to match on and whether they should be matched using the fuzzy algorithms (similar) or matched exactly:

So not too dissimilar to SSIS. You then choose to give each domain a percentage weight, which must add up to 100%. For example, if you give Full Name a weight 30% and, when run, its match score is 61% accuracy, then you have Address Line 1 at a 70% weight and a 45% accuracy, then an overall match score for a row will be (0.3 * 0.61) + (0.7 * 0.45) = 49.8% match score.

Starting off with a 50% weight on the two domains, I get the following results when operating on the same data:

As with SSIS, it hasn’t got it quite right, but using the Gender domain will help. What I quite like in DQS is that I can flag a domain as being a pre-requisite:

Flagging a domain as a pre-requisite means that the domain will be used to eliminate matches, but will not contribute to the matching score. If I wanted it to contribute to the matching score I would just have to uncheck pre-requisite and give it a weight. Running the matching again gives the following results:

So the duplicate is correctly mapped with a score of 75%. Drilling down on the match was achieved is quite nice in DQS also:

This brings me to an interesting observation about the matching. I originally had my dummy record with a value of “3 Pierr Demoulainn” for the address. So in addition to having a typo in the second name and missing the word “Rue”, my other test also missed the second e out of the word “Pierre”. If I run my test data with these values, I get no matches at all. The lack of the second e in “Pierr” seems to have made all the difference. I can get it to match if I give a lot of weighting to Full Name. If I do this, we see the following results:

Address Line 1 has been given a score of 0%. Making the same change to my test data and then running in SSIS gives different results. The similarity for the address drops from 0.81 to 0.75, but it certainly doesn’t drop to zero. Although it will depend massively on your own data, the matching in DQS seems a bit more stringent. This can be mitigated by using more domains for the matching, rather than the simple 2 or 3 domains that I’ve used. To back that statement up, using a composite domain on both Address Line 1 and City does yield a strong match (71%) for the same data.

Conclusion

The rule based matching in DQS gives a lot of flexibility on how to weight matching across different domains. Coupled with the knowledge base components that interact with the matching, such as cleaning and Term-Based Relations, DQS has more features specifically aimed at matching than SSIS. It all depends on what sort of data you’re working with, but in theory, as you maintain the knowledge base over time, DQS should give you strong matches based on the knowledge you’ve given it.

However, there are some drawbacks – it’s a shame that the matching can’t be automated (please vote for my connect suggestion if you agree). SSIS by its very nature can be automated, meaning the matching in SSIS will be more suitable for a lot of implementations. Secondly, I’d like to be able to match external records against correct values already in the knowledge base, rather than just doing a fuzzy grouping on the external records. I’m a bit surprised DQS can’t do a fuzzy lookup, maybe this will change in V2. Finally, as I’ve shown above, some of the matching results are a little unexpected, whereas in SSIS they were good pretty much straight away. To be fair to DQS this could probably be mitigated with a better choice of matching fields and also running cleaning against a fully populated knowledge base beforehand.

Mean, Median and Mode in MDX

Jeremy Kashel — Mon, 19 Mar 2012 11:25:28 GMT

I recently delivered an Analysis Services training course to a client who were a bit surprised to learn that the standard list of Aggregation Functions for an Analysis Services measure doesn’t include Mean, Median and Mode. My answer was of course that all three can delivered using MDX, so I thought it might be a good blog post to show how it’s done.

Before I begin, just a quick refresher for anyone who needs it, the following article seems to be a good reference on the difference between Mean, Median and Mode. Essentially Mean is the average, Median is the middle value, whereas Mode is the value that occurs the most.

Mean and Median

The Mean and the Median are both relatively easy to do in MDX as there are built-in functions for both AVG() and Median(). As they’re well documented here and here, I won’t spend too long covering them. In the following MDX query I’m producing the Mean and the Median of the product sub categories in Adventure Works:

WITH 
MEMBER  [Measures].[Mean] AS AVG([Product].[Subcategory].[Subcategory].Members,
        [Measures].[Internet Sales Amount])

MEMBER  [Measures].[Median] AS Median([Product].[Subcategory].[Subcategory].Members,
        [Measures].[Internet Sales Amount])

SELECT  {[Measures].[Internet Sales Amount], [Measures].[Mean], [Measures].[Median]} ON 0,
        NonEmpty([Product].[Subcategory].[Subcategory].Members, [Measures].[Internet Sales Amount]) ON 1
FROM    [Adventure Works]
WHERE   ([Date].[Date].&[20070727])

If you run the query and copy the data out to Excel you’ll see that it matches the result of Excel’s Median and Mode functions.

Mode

Mode is harder to achieve as there’s no built in MDX function. Fortunately, Excel is on hand to help out, as Analysis Services allows you to use some of the Excel functions in MDX. There are warnings that come with this approach, as you need to have Excel installed on the server and there can also be performance problems. But, if you’re comfortable with these caveats, then the Excel mode function may be an option for you.

If you do use the Excel Mode function then there are a few things to watch out for. Firstly, you’ll have to use the MDX SetToArray() function to pass the set in the format that Excel expects. Secondly, you may get an error returned with the description: “#Error The following system error occurred: Invalid flags.” This is due to the data type of the Measure – it seems that Currency measures are not supported by this function. Therefore, I’m using Cdbl() on Internet Sales Amount to get this to work:

WITH 
MEMBER  [Measures].[Demo] AS Cdbl([Measures].[Internet Sales Amount])
    
MEMBER  [Measures].[ExcelMode] AS
        Excel!MODE(SetToArray(NONEMPTY([Product].[Subcategory].[Subcategory].Members, [Measures].[Demo])
        * [Measures].[Demo]))

Applying this to the example query I’ve got above, the mode returned is 8.99, as its the only number that appears twice.

Bi Modal Result Set

There is a bit more complexity to Mode in some situations, essentially as you can get a bi-modal result set - where there is more than one value occurring the most. E.g. the mode of 1,2,2,3 is just 2, but the mode of 1,2,2,3,3,4 is both 2 and 3 – so it’s known as bi-modal.

The Excel Mode function won’t help in this scenario, as it will just return one of the mode values. Interestingly Excel 2010 does support this, with the Mode.Mult() function, but I’ve been unable to get this to work, presumably as the Excel function returns an array.

All is not lost though, it’s possible to produce mode using MDX. First of all, the numbers that I’m operating on are shown below. As you can see, there are two sets of two numbers that are the same:

As the following forum thread shows, mode can be achieved using MDX. I’m adapting that approach to give the following MDX:

WITH 
--Produce a result set that will guarantee bi modal results
MEMBER    [Measures].[Demo] AS
        CASE WHEN [Product].[Subcategory].CurrentMember IS [Product].[Subcategory].&[31]  THEN 65.91
        ELSE Cdbl([Measures].[Internet Sales Amount]) END

--Count how often each value appears
MEMBER [Measures].[ValueCount] AS 
SUM( 
    Union([Product].[Subcategory].CurrentMember.Level.Members,
        {[Product].[Subcategory].CurrentMember} AS Currentsub)
    , IIF(([Product].[Subcategory].CurrentMember, [Measures].[Demo]) = 
        (Currentsub.Item(0).Item(0), [Measures].[Demo]), 1, null)
)

--Only get the items that appear the most
SET [MaxModes] AS 
    ORDER(FILTER(NONEMPTY([Product].[Subcategory].[Subcategory].Members, {[Measures].[Demo]}),
    [Measures].[ValueCount] = MAX(NONEMPTY([Product].[Subcategory].[Subcategory].Members, [Measures].[Demo]), 
        [Measures].[ValueCount])), [Measures].[Demo], ASC)

SELECT   {[Measures].[Demo], [Measures].[ValueCount]} on 0,
        [MaxModes]
        --Filter out the duplicates 
        HAVING [MaxModes].CurrentOrdinal = 0 OR [Measures].[Demo] <> 
            ([Measures].[Demo], [MaxModes].Item([MaxModes].CurrentOrdinal - 2)) ON 1
FROM    [Adventure Works]
WHERE    ([Date].[Date].&[20070727])

This gives the following correct result set, assuming we just want the two bi modal values:

An alternative is to use an MDX Filter() or HAVING clause to just display all the sub categories that have the mode values, which would just require a small modification to the above code.

I’ve not used these approaches with big data volumes etc, but they should at least give you a few options if you’ve got to do these sort of calculations in your own environment.

Power View Default Field Set

Jeremy Kashel — Thu, 23 Feb 2012 11:57:10 GMT

Here's another quick tip when creating data models for use with Power View - Default Field Sets can be created in both Tabular SSAS and PowerPivot that give the user a shortcut for automatically adding multiple fields to a report.

As an example, here's a screenshot of the Default Field Set in PowerPivot - this will tell Power view that the following fields should be automatically selected when the table is added to a Power View report:

I'd seen this in the documentation a while back, but hadn't actually used it, nor could I see a way to select the table. In fact, although there's no checkbox, it's just a simple single click on the actual table name, as I’ve highlighted below:

When you click on the table name Product above, you will now get the following fields automatically added to a table visualisation:

So not exactly rocket science, but worth doing! Here's how to set up the Default Field set for PowerPivot and Tabular Analysis Services.

PowerPivot Settings for Power View

Jeremy Kashel — Mon, 20 Feb 2012 16:30:59 GMT

I’ve been using both PowerPivot and Power View quite a bit recently and, in addition to the post I did a while back, have made a few further observations on getting the two to play nicely together.

Building an Example PowerPivot Model

For this post I’ve created a very very simple PowerPivot model, based on the Adventure Works Internet Sales Fact table and its related tables. What this means is that after the model is built, I can build a variety of Excel reports that summarise the measures in the FactInternetSales Adventure Works fact table, such as Sales Amount, Tax Amount or Order Quantity. In the example below I’ve built a very basic report showing Order Quantity by year:

On to Power View

Having already published the workbook to SharePoint, my task now is to create a Power View report, which can be done via the PowerPivot Gallery. If wanted to try and create a visual version of the above report in Power View (E.g. a graph), my steps would be to do first of all drag Calendar Year into the report, then Order Quantity. However, when doing this recently, the result wasn’t quite what I expected:

Order Quantity has not been summed at all, which is the opposite behaviour to that of PowerPivot. Consequently I can’t create a graph visualisation, because Power View thinks I don’t have any measures in my report:

Yet if we contrast this to measures such as Internet Sales Amount or Tax Amount, they all work fine. Upon looking at the field list for Internet Sales, we will see that Order Quantity is missing the sum symbol, as Power View has not interpreted it as a measure:

We can get around this in Power View with a bit of dragging and dropping, but we may as well look why it’s happened in the first place. The reason for this is the way in which the PowerPivot import wizard interprets SQL data types. The OrderQuantity column in the FactInternetSales SQL table has a data type of Smallint, which will not be summarised by default in Power View. Decimal and Money types are fine, hence why Tax Amount and Sales Amount above look ok.

Rather than changing the data types, we can force Power View to interpret the column as a measure by going to the PowerPivot advanced tab and clicking on Summarize By. If the Summarize By value is changed from Default to Sum, then Power View will interpret the column as a measure:

If we republish the model and then go back to Power View, we will see quite a difference with the same actions as before. First of all the field list is looking correct:

Secondly, only dragging Calendar Year and Order Quantity will now give the following correct results:

Now that we have a measure, it means that we can now change the table into a visualisation of our choice:

Power View is the kind of product that power users are hopefully going to find intuitive, but we can make things even easier for users by making small changes like this.

Data Quality Services (RC0) - Cleanse and Train the KB

Victor Mendes — Mon, 09 Jan 2012 16:10:00 GMT

This post will show a new KB (Knowledge Base) being trained using the new feature via domain management which allows the KB to import the knowledge gained from a cleansing project. In the previous release, CTP3, the KB was trained using Knowledge Discovery from either a table or excel file. This could also have been done interactively via domain management, but not directly from a cleansed project.

In this example we will retrain the new KB by importing knowledge learnt from two cleansed samples of data. The outputs of the cleansed projects will be imported into the KB. Before retraining our KB we will need to tweak one of the cleansing configuration settings. This will retrain our new KB faster.

New Knowledge Base

If you have not created a KB before a step by step guide can be found here.

The new KB was called UK Counties. A single domain value was used and it was called UK County (Full Name).

Using the Knowledge Discovery feature we first trained our empty KB with a unique list of UK Counties. The Domain Management feature could also have been used by entering domain values or importing them from an excel file.

Training the KB doesn't stop there, further training is required to expand the KB knowledge. Interactively we could add new domain values or synonyms to the already existing values such as Bucks for Buckinghamshire if we knew this was a potential synonym. For this example we will be using the knowledge gained from our cleansing project to retrain our KB.

Below is a screen shot of some of the domain values prior to retraining and being published to the DQS server.

Configuring Cleansing Projects

During cleansing a computer-assisted process will analyse how the source data conforms to the knowledge in the KB and a confidence level is determined. There are two confidence level thresholds: suggestions and auto corrections. Suggestions can be approved or rejected. Auto corrections are already approved, but it is possible to reject them as well.

These thresholds can be set in the general settings for a cleansing project from the DQS configuration area.

By default 0.6 (60%) and 0.8 (80%) are set respectively for suggestions and auto corrections.

As the KB is still young we need to retrain it with some samples of our real data and therefore make more suggestions. Hence we will reduce the confidence level percentage for suggestions down to 0.4 (40%).

N.B. This can go the other way where too many suggestions are given or a source value could be mapped to a completely incorrect domain value. As the knowledge of KB increases the confidence level for suggestions should also be increased to isolate new values and minimise interactive cleaning.

Training the KB

There are a number of ways to train the KB

Knowledge Discovery, or
Domain Management by either
1. Interactively entering or editing domain values,
2. Importing domain values from an excel file, or
3. Using the completed output of a cleansing project.

The final option is the new feature added to RC0 which makes managing knowledge within the KB simpler.

Let’s look at our first sample of data.

You will notice a number of data quality issues such as postcode and county, town and county or a short county name. Our objective is to clean this data to the County Full Name domain value in the KB.

If you have not created a data quality project before a step by step guide can be found here.

We will skip to the end of our cleansing project and analyse the results.

On successful completion we can see from the results that 0 records were corrected and 7 records have suggested domain values. We can now perform an interactive cleanse by which we approve or reject the suggestions made. Where the data confidence level has not been met for suggestions of 40% or auto corrections of 80% it will be considered as a new value. For this particular example we should not be expecting new values as we have a definitive list of counties and therefore we will need to add corrected values manually.

Let’s start with the suggested values. All are correct and therefore only need to be approved. We approve all values by clicking the check box under the Approve column or we can click the Approve All icon . All suggested values will move into the Corrected tab area.

Now let’s review the new tab below. Three new values in the source data have been found, the analysis could not confidently associate any of the three values with a confidence level greater than or equal to 40% and therefore has presented them as new values.

Looking at the values we know the county is Middlesex as is in the KB, hence we manually enter this value into the Correct to column. Now we can approve each value or we can click the Approve All icon.

All values have now moved to the corrected tab.

Our data cleanse results are now ready for final review before finishing the project. Finishing a project no longer allows the project to be re-run for cleansing purposes as the knowledge gained would be lost, however you will be able to interactively clean to alter the final results. It’s these results and knowledge that is imported back into the KB, but the project must be marked as finished.

It is the CountyName_Source that we want to feed back to the KB as synonyms for the domain values found or modified in the CountyName_Output.

To feed this new acquired knowledge we open our KB via Domain Management and can now use the new feature Import project values.

We select the finished cleansing project and uncheck Add values from New Tab.

Only the new knowledge from the project has been added to the KB. The Show Only New check box is checked automatically.

If we uncheck the Show Only New check box and scroll down to London and Middlesex you will see the synonyms for these counties have been added to the KB.

We now publish the KB to the DQS server and are ready to analyse another data sample.

As above we create a new cleansing project and analyse the data. The newly found knowledge from the previous cleansing project has corrected five values with 4 values suggested, leaving one value as new.

If we view the reason column for the corrected tab, four of the values were associated with the synonyms of domain values within the KB from the knowledge learned. 'London.' was cleansed and had a confidence greater than or equal to 80% and therefore auto corrected.

In the suggested tab the correct to values are all correct and only need approving.

The new value failed the confidence level of 40%, however it could have been found and a suggestion made if the threshold was reduced further. This is not a problem as the correct value was entered and approved.

Finally we review our results and finish the project.

We now feed this new knowledge back into the KB via Domain Management.

The new synonyms will now be added to the existing KB domain values.

This has been a simple example on how to train and retrain a new KB. By having a definitive list of counties the need to analyse our data prior to creating the KB is somewhat reduced.

When building a KB it is important to acquire knowledge from samples of source data using any of the features available. From this example the output of a cleansing project provides a simpler method where the confidence level for suggested values is much lower. Once the KB contains sufficient knowledge for cleansing the confidence level can be increased and larger sets of data can be cleansed.

DQS is a great tool for the power user to maintain content with little input from IT. In posts to follow I will also review Matching projects and the SSIS DQS component.

Master Data Services in SQL Server 2012 RC0

Jeremy Kashel — Wed, 30 Nov 2011 13:31:34 GMT

There’s been a whole host of changes to MDS in the SQL Server 2012 RC (Release Candidate) 0 that came out the other week. This blog post gives an overview of the changes, before diving into detail on a few of them. At a high level, the following changes have been made to MDS:

Improved Master Data Manager front screen UI and navigation paths
Collections interface updated to Silverlight
Improved Excel user interface & functionality
Auto generation of entity code values, without using business rules
New deployment tool
SharePoint integration

Let’s take a look at each of these changes:

Improved Master Data Manager UI

Although the UI improvement (shown below) is good, the best thing about the Master Data Manager changes, in my opinion, is that clicking on the Explorer feature no longer takes you into the Model View, but instead takes you straight into your master data for your core entity (e.g. in a Customer model this entity would be Customer). I’m not sure if this would get a bit frustrating if you didn’t want the core entity, but then again everything seems very quick in RC0, so I don’t think it’s really going to matter.

There’s also a new button next to a domain-based attribute that apparently has been designed to help with Many-to-Many relationships. I can definitely see that working, but it’s useful to have anyway to jump to the member details for the domain based attribute that you are viewing, many-to-many or not:

Collections Interface Updated to Silverlight

The collections interface is now much slicker, getting the Silverlight makeover seen in other areas of Master Data Manager. Switching between collections, for example, which could take a while in R2, now happens very quickly, making collections far more useable. The screen shot below shows how you edit collection members by picking members from one of the entities and adding those over into the collection by clicking the Add button:

Collections now have the concept of a Weight, meaning that you can alter the weighting value associated with the collection members, which could be useful for reporting purposes. The idea is that you extract the collection members and the weight values in a subscription view. A Weight column is actually included in the 2008 R2 collection subscription views, but there was no front end to modify the weight value, which has of course now changed:

Improved Excel User Interface and Functionality

The MDS Excel Ribbon has been given a makeover, meaning that you now see the following in the ribbon:

It’s interesting to see that the Favourites section has been replaced with the concept of ‘queries’. The idea is that you can send a query file via email to another user, providing that user has the Excel add-in. When the user double clicks on the query file (extension *.mdsqx), Excel will open and make a connection to MDS, using the connection and filter information provided in the file. This will result in Excel opening, with the user prompted if they would like to connect to MDS:

It’s also good to see that a few of the domain-based attribute issues have been addressed, namely the display of domain-based attribute names when you filter an entity and also the display of the names and the codes together in the Excel sheet. Here’s a screen shot of the how the attribute names are now visible when filtering the Country domain-based attribute that exists in the Customer model:

Auto Generation of Entity Code Values

If you wanted the code to be auto-generated in 2008 R2, you had to use a business rule. You can still do that if you want, but the create entity admin screen has changed slightly to include an option to auto generate the code. This works slightly better than business rules in my opinion, at least as far as the Excel add-in is concerned, as the code is returned to the user immediately after publishing, whereas the business rules require you to do a refresh in Excel and of course need more development! Here’s a screenshot of the add entity screen:

New Deployment Tool

Deployment has been altered in SQL 2012, with the addition of a new deployment tool, plus the fact that subscription views now get deployed. It seems that the current R2 deployment method (in the Administration section of Master Data Manager) is still included but now will not deploy data. To do that you need to use MDSModelDeploy.exe, as explained here. As an example, here’s how you can deploy the sample Customer model using MDSModelDeploy.exe, for default installations:

MDSModelDeploy deploynew –package “C:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\customer_en.pkg” –model “Customer” –service “MDS1”

SharePoint Integration and Further Details

Master Data Manager now supports a set of parameters that allow the MDS UI to be displayed without the header, menu bar and padding area. This means that MDS can now be incorporated into SharePoint or other websites. For the details on this, as well as more details on the above points, take a look at the following Technet article.

Working with Images in Power View

Jeremy Kashel — Tue, 29 Nov 2011 13:04:31 GMT

Power View includes several ways to create visualisations with images, ranging from a simple table, to the scrollable tile visualisation. In this post I’m going to explain a bit about the tile visualisation, showing how it can be used in Power View, before showing a couple of tips for working with images in both PowerPivot and Analysis Services Tabular models.

Power View Tiles

The Power View Tiles visualisation allows you to display a scrollable series of images, which, when clicked on, will display data of your choosing. The report below is an example of this:

What’s nice about the View above is that the names of the products are automatically displayed underneath the images. This is a sensible place to display the product names, as it means the area below can focus on whatever product level data you want, in this case Revenue by Month for the selected product.

This works fine in the sample models (in my case the Tailspin Toys model that I think came with CTP3) but it wont work in vanilla PowerPivot / Tabular models unless you configure a few model settings. Trying to reproduce the report above without these settings will give the following tile visualisation, minus the product names:

PowerPivot Advanced Settings

To address this in PowerPivot, you first of all need to switch to Advanced Mode, which you can do by selecting this option from the File menu in PowerPivot:

This gives you the following advanced tab:

Clicking on the highlighted Table Behaviour button in the ribbon gives you the following Table Behaviour window:

Here you can “change the default behaviour for different visualisation types and default grouping behaviour in client tools for this table”. The following properties should be set in order to get the tiles working correctly:

Row Identifier – Specifies a column that only contains unique values, which allows the column to be used as an internal grouping key in client tools.
Default Label – Specifies which column gives a row level display name, e.g. the product name in a Product table. This is key, as when a row level image is used, this property specifies which value to display alongside the image in tiles and other image-based visualisation.
Default Image – Specifies which column contains images representing the row level data, e.g. pictures of products.

The full details for all these properties can be found here.

Tabular Models

Tabular Models contain the same properties, which can be edited when you open a tabular project in the new SQL Server Data Tools. The image below shows how the properties have been configured for the column in my model called Image:

Back to Power View

If we now build a tile visualisation using the image column, we will see that we get the names of the products returned along with the image. It’s also worth noting that the Image and Product Name columns have now have an icon next to them, indicating that the field exhibits “row level” behaviour:

Finally, for some examples of what’s new in Power View SQL 2012 RC0 take a look at this post on the Reporting Services Team Blog.

SQL Server 2012 Running Totals

Martyn Bullerwell — Wed, 16 Nov 2011 15:48:19 GMT

SQL server has sometimes come under fire with the Oracle vs SQL Server debate because of the lack of some of the more advanced (and less used) functionality that is outlined in the SQL ANSI standards. An example of this is Window Functions, which became an ANSI standard under the ANSI:2003 revision. A Window function is an aggregate function that can be applied to a subset of a full set of data. Now this can be achieved in current versions of SQL Server (2008 R2 and its predecessors), but not using Window Functions and therefore has a performance implication. There are about 3 or 4 approaches to achieving a running total in SQL Server prior to the 2012 version, however none particularly elegant.

For my following example I will outline how to perform a running total using AdvertureWorks2008 sample data. I will remind us of 1 of the method's (probably the most common) of how we used to do running totals prior to SQL Server 2012, and then show how to do the same running total using a SQL Server 2012 Window Function. To set the scene, we will be looking for a running total of Line Items for a given Order. This may be kind of query you may wish to write to generate an invoice with a running total on it.

To begin with lets look at the more traditional query:

SELECT
 A.SalesOrderID,
 A.SalesOrderDetailID,
 A.LineTotal,
 SUM(B.LineTotal)
FROM
Sales.SalesOrderDetail AS A
 CROSS JOIN Sales.SalesOrderDetail AS B
WHERE
 B.SalesOrderDetailID <= A.SalesOrderDetailID
 AND
 A.SalesOrderID = B.SalesOrderID
GROUP BY
 A.SalesOrderID,
 A.SalesOrderDetailID,
 A.LineTotal
ORDER BY
 A.SalesOrderID,
 A.SalesOrderDetailID,
 A.LineTotal

This simply works by self joining up to certain point, so works fine for any data that can be ordered easily, as running totals usually are this method usually suffices.

The following query uses the new Window function in SQL Server 2012:

SELECT
    SalesOrderID,
    SalesOrderDetailID,
    LineTotal,
    SUM(LineTotal)
        OVER     (PARTITION BY
                SalesOrderID
            ORDER BY
                SalesOrderDetailID) AS OrderRunningTotal
FROM
    Sales.SalesOrderDetail
ORDER BY
    SalesOrderID,
    SalesOrderDetailID,
    LineTotal

You will instantly notice that the second query is far more elegant, and more simplistic to understand. In short, in this query, we are telling SQL server to Sum the “Line Total” over an ordered partition of the data. Both queries return the same result, however it becomes interesting when we look at the execution plans, relative to each other. This is shown below (apologies for the size of these):

What is important to note that the second (bottom) plan is smaller, and simpler. the other, very significant point is that the Query Cost (relative to batch) is a whopping 97% for the old method of running totals, meaning that the new Windowing Functions are far more efficient.

In summary, I believe that this is an example of where SQL server is becoming a firm competitor to some of its perceived rivals, its one of the few points that can be raised as a valid point in the argument of SQL Server vs Oracle, but not any more!

SQL Server 2012 : Columnstore Index in action

sachatomey — Wed, 09 Nov 2011 16:17:40 GMT

One of the new SQL Server 2012 data warehouse features is the Columnstore index. It stores data by columns instead of by rows, similar to a column-oriented DBMS like the Vertica Analytic Database and claims to increase query performance by hundreds to thousands of times.

The issue with indexes in a data warehouse environment is the number and broad range of questions that the warehouse may have to answer meaning you either have to introduce a large number of large indexes (that in many cases results in a larger set of indexes than actual data), plump for a costly spindle-rich hardware infrastructure, or you opt for a balanced hardware and software solution such as a Microsoft SQL Server 2008 R2 Fast Track Data Warehouse or a HP Business Data Warehouse Appliance where the approach is ‘index-light’ and you rely on the combination of high throughput and performance power to reduce the dependency on the traditional index.

The Columnstore index is different in that, when applied correctly, a broad range of questions can benefit from a single Columnstore index, the index is compressed (using the same Vertipaq technology that PowerPivot and Tabular based Analysis Services share) reducing the effort required on the expensive and slow disk subsystem and increasing the effort of the fast and lower cost memory/processor combination.

In order to test the claims of the Columnstore index I’ve performed some testing on a Hyper-V instance of SQL Server 2012 “Denali” CTP3 using a blown up version of the AdventureWorksDWDenali sample database. I’ve increased the FactResellerSales table from approximately 61,000 records to approximately 15.5 million records and removed all existing indexes to give me a simple, but reasonably large ‘heap’.

Heap

With a clear cache, run the following simple aggregation:

SELECT
    SalesTerritoryKey
    ,SUM(SalesAmount) AS SalesAmount
FROM
   [AdventureWorksDWDenali].[dbo].[FactResellerSales]
GROUP BY
    SalesTerritoryKey
ORDER BY
    SalesTerritoryKey

Table 'FactResellerSales'. Scan count 5, logical reads 457665, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 7641 ms, elapsed time = 43718 ms

Non-Clustered Index

Before jumping straight in with a columnstore index, let’s review performance using a traditional index. I tried a variety of combinations, the fastest I could get this query to go was to simply add the following:

CREATE NONCLUSTERED INDEX [IX_SalesTerritoryKey] ON [dbo].[FactResellerSales]
(
   [SalesTerritoryKey] ASC
)
INCLUDE ([SalesAmount]) WITH
(
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, DATA_COMPRESSION = PAGE
) ON [PRIMARY]
GO

Notice I have compressed the index using page compression, this reduced the number of pages my data consumed significantly. The IO stats when I re-ran the same query (on a clear cache) looked like this:

Table 'FactResellerSales'. Scan count 5, logical reads 26928, physical reads 0, read-ahead reads 26816, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6170 ms, elapsed time = 5201 ms.

Much better! Approximately 6% of the original logical reads were required, resulting in a query response time of just over 5 seconds. Remember though, this new index will really only answer this specific question. If we change the query, performance is likely to fall off the cliff and revert back to the table scan.

Incidentally, adopting an index-light ([no index]) approach and simply compressing (and reloading to remove fragmentation) the underlying table itself, performance was only nominally slower than the indexed table with the added advantage of being able to perform for a large number of different queries. (Effectively speeding up the table scan. Partitioning the table can help with this approach too.)

Columnstore Index

Okay, time to bring out the columnstore. The recommendation is to add all columns into the columnstore index (Columnstore indexes do not support ‘include’ columns), practically there may be a few cases where you do exclude some columns. Meta data, or system columns that are unlikely to be used in true analysis are good candidates to leave out of the columnstore. However, in this instance, I am including all columns:

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_Columnstore] ON [dbo].[FactResellerSales]
(
    [ProductKey],
    [OrderDateKey],
    [DueDateKey],
    [ShipDateKey],
    [ResellerKey],
    [EmployeeKey],
    [PromotionKey],
    [CurrencyKey],
    [SalesTerritoryKey],
    [SalesOrderNumber],
    [SalesOrderLineNumber],
    [RevisionNumber],
    [OrderQuantity],
    [UnitPrice],
    [ExtendedAmount],
    [UnitPriceDiscountPct],
    [DiscountAmount],
    [ProductStandardCost],
    [TotalProductCost],
    [SalesAmount],
    [TaxAmt],
    [Freight],
    [CarrierTrackingNumber],
    [CustomerPONumber],
    [OrderDate],
    [DueDate],
    [ShipDate]
)WITH (DROP_EXISTING = OFF) ON [PRIMARY]

Now when I run the query on a clear cache:

Table 'FactResellerSales_V2'. Scan count 4, logical reads 2207, physical reads 18, read-ahead reads 3988, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 235 ms, elapsed time = 327 ms.

I think the figures speak for themselves ! Sub-second response and because all columns are part of the index, a broad range of questions can be satisfied by this single index.

Storage

The traditional (compressed) non-clustered index takes up around 208 MB whereas the Columnstore Index comes in a little less at 194 MB so speed and storage efficiency, further compounded when you take into account the potential additional indexes the warehouse may require.

So, the downsides? Columnstore indexes render the table read-only. In order to to update the table you either need to drop and re-create the index or employ a partition switching approach. The other notable disadvantage, consistently witnessed during my tests, is the columnstore index takes longer to build. The traditional non-clustered index took approximately 21 seconds to build whereas the columnstore took approximately 1 minute 49 seconds. Remember though, you only need one columnstore index to satisfy many queries so that’s potentially not a fair comparison.

Troubleshooting

If you don’t notice a huge difference between a table scan and a Columnstore Index Scan, check the Actual Execution Mode of the Columnstore Index Scan. This should be set to Batch, not Row.

If the Actual Execution Mode is reporting Row then your query cannot run in parallel:

- Ensure, if running via Hyper-V, you have assigned more than one processor to the image.
- Ensure the Server Property ‘Max Degee of Parallelism’ is not set to 1.

Summary

In summary, for warehousing workloads, a columnstore index is a great addition to the database engine with significant performance improvements even on reasonably small data sets. It will re-define the ‘index-light’ approach that the SQL Server Fast Track Data Warehouse methodology champions and help simplify warehouse based performance tuning activities. Will it work in every scenario? I very much doubt it, but it’s a good place to start until we get to experience it live in the field.

Managing SSAS Named Sets with Master Data Services Collections

Jeremy Kashel — Mon, 07 Nov 2011 17:04:00 GMT

Master Data Services Collections are probably not the first feature that come to mind when you think of the MDS product. The hierarchies and member grids tend to be the core functionality, but as this post will hopefully show, MDS Collections are useful also.

Collections are essentially managed lists of members that can come from multiple different MDS explicit hierarchies, or also from another collection. The idea is that this “subset of members” can be maintained in MDS by a business user and then fed to external applications for reporting or other purposes.

Analysis Services Named Sets

One example of how collections can be used is to maintain Analysis Services named sets. Some named sets, such as a Top 50 Customers, don't require any maintenance, as it’s just the top 50 of all customers, based on a particular measure. On the other hand, sometimes named sets can be static lists, e.g. a list of core products that the user wants to see for reporting.

In the latter example, if a user wants the definition of a named set to change, they have to get IT to change the MDX script. MDS collections can help by allowing the user to control the named set definition, reducing the burden on IT.

Example Scenario

Here’s an example of how this is done. First of all, the end game is that I have a named set in Analysis Services that is currently just for 3 products. Therefore, a user can easily drop this set into an Excel report to get quick access to the products that are important to them:

So the challenge is that we need to find some way of extending this named set without doing it manually. This is where MDS starts to come in, so using the sample Product model that comes with MDS, I’ve created a new collection called Favourite Products, as shown in the image below:

If I go and edit the Favourite Products collection in MDS, then I can drag and drop more products into this collection, or remove some existing members. In this case, I’ve chosen to add two new products:

C#, AMO and the MDS API

So the named set represents the target that we need to get to, whereas the MDS collection that’s shown is the source. To get the contents of the MDS collection to update the named set, one way of doing it is to use the MDS API to pick up the collection members, and then to use AMO in order to write the named set into the MDX script. I’m just doing this in a C# windows application, but you could do it via a batch process, such as SSIS. For this post I’m just going to show the code, so here goes:

This post is already starting to feel too long so I’m not going to show the basics of the MDS API. For that, take a look at a good posting by the MDS Team blog here. Also, as anyone who knows me will no doubt agree, I don’t tend to get involved in doing C#, so don’t consider this to be production ready! It should give you an idea of the basics though. Anyway, assuming that we’re now connected to the MDS Web Service, I’ve created the following method that will return the members from the collection:

private HierarchyMembers ObtainHierarchyMembers(string entityId, string hierarchyId, string modelId, string versionId)
{
    HierarchyMembersGetRequest request = new HierarchyMembersGetRequest();
    HierarchyMembersGetResponse response = new HierarchyMembersGetResponse();
    request.HierarchyMembersGetCriteria = new HierarchyMembersGetCriteria();

    //State that our hierarhcy type is a collection and that we want collection members
    request.HierarchyMembersGetCriteria.HierarchyType = HierarchyType.Collection;
    //Pass in the key search criteria to identify the correct collection in MDS
    request.HierarchyMembersGetCriteria.ParentEntityId = new Identifier { Name = entityId };
    request.HierarchyMembersGetCriteria.HierarchyId = new Identifier { Name = hierarchyId };
    request.HierarchyMembersGetCriteria.ModelId = new Identifier { Name = modelId };
    request.HierarchyMembersGetCriteria.VersionId = new Identifier { Name = versionId };

    request.HierarchyMembersGetCriteria.RowLimit = 50;
    request.International = new International();
    OperationResult result = new OperationResult();
    //Return the hierarchy members from the service
    return service.HierarchyMembersGet(request.International, request.HierarchyMembersGetCriteria, out result);
}

Before we use the above method, we need to connect to SSAS using AMO. That can be done quite easily with the following code:

Server server = new Server();
string connection = "Data Source=.;Catalog=Adventure Works DW 2008R2;";
server.Connect(connection);

After we’ve done all the usual error handling associated with connecting to a database, we need to pick up the SSAS database and cube:

Database database = server.Databases["Adventure Works DW 2008R2"];
Cube cube = database.Cubes["Adventure Works"];

Now we’re ready to call our ObtainHierarchyMembers method, before looping through it to build the named set:

StringBuilder mdx = new StringBuilder("\n//Auto generated named set at " + DateTime.Now.ToString() + 
    "\nCREATE SET CurrentCube.[Favourite Products] AS {");
int count = 1;
//Loop through the collection to build the mdx
foreach (ParentChild pc in hm.Members)
{
    //Add the members to the MDX string
    //This references the member by name
    //It would be possible to reference by member key, but would require more work
    mdx.Append("[Product].[Product].[" + pc.Child.Name + "]");
    if (count < hm.Members.Count())
    {
        mdx.Append(", ");
    }
    count++;
}
mdx.Append("};");

Now we need to insert the named set in the correct place within the existing MDX script, bearing in mind it could already exist:

string currentScript = cube.MdxScripts[0].Commands[0].Text;
//Find the correct place to insert the named set within the MDX script:
int start = currentScript.IndexOf("\n//Auto generated named set at");
int end = 0;
StringBuilder newScript = new StringBuilder(currentScript);
if (start != -1)
{
    end = currentScript.IndexOf(";", start);
    //If the named set already exists, remove it
    newScript.Remove(start, end - start + 1);
}
else
{
    start = currentScript.Length;
}
//Insert the named set in the correct place
newScript.Insert(start, mdx.ToString());
//Update the cube's MDX script
cube.MdxScripts[0].Commands[0].Text = newScript.ToString();

Finally we just need to update the cube in order to write the MDX back to the cube:

//Call the update methods to update the cube
cube.MdxScripts[0].Update();
cube.Update();

User Reports

Now that the cube has been updated, if the Excel report is refreshed, then the two new products will appear in the list:

Summary

Controlling SSAS named sets like this won’t be for everybody. I’ve certainly worked in a few clients where strict process has to be followed to update this sort of thing, but I can think of other companies that I know where this would be really useful.

Managing Analysis Services named sets is just one use for collections. Another example might be managing default multi-select parameters for SSRS reports. As collections are just lists of members that can be extracted easily, what you do with them is up to you!

SQL Server 2012 Licensing

sachatomey — Thu, 03 Nov 2011 16:48:00 GMT

Today saw the announcement of how SQL Server 2012 will be carved up and licensed, and it's changed quite a bit. There are three key changes:

1) There's a new Business Intelligence Edition that sits between Standard and Enterprise
2) No more processor licensing. There's a move to Core based licensing instead (with a minimum cost of 4 cores per server)
3) Enterprise is only available on the Core licensing model (Unless upgrading through Software Assurance *)

Enterprise, as you would expect, has all the functionality SQL Server 2012 has to offer.

The Business Intelligence edition strips away
- Advanced Security (Advanced auditing, transparent data encryption)
- Data Warehousing (ColumnStore, compression, partitioning)
and provides a cut-down, basic (as opposed to advanced) level of High Availability (AlwaysOn).

In addition, the Standard Edition removes
- Enterprise data management (Data Quality Services, Master Data Services),
- Self-Service Business Intelligence (Power View, PowerPivot for SPS)
- Corporate Business Intelligence (Semantic model, advanced analytics)

If you are utilising 4 core processors, licence costs for Standard ($1,793 per core, or $898 per Server + $209 per CAL) and Enterprise ($6,874 per core) remain similar (ish). However, you will be stung if you have more cores. The Business Intelligence edition is only available via a Server + CAL licence model and it's apparent that Microsoft are placing a big bet on MDS/DQS, Power View, PowerPivot for SharePoint and BISM as the licence for the Business Intelligence edition is $8,592 per server, plus $209 per CAL, that's nearly 10x more per server than Standard Edition !

For the complete low-down check out these links:

Editions Overview:
http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx

Licensing Overview:
http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-licensing.aspx

Licence Detail (including costs):
http://download.microsoft.com/download/D/A/D/DADBE8BD-D5C7-4417-9527-5E9A717D8E84/SQLServer2012_Licensing_Datasheet_Nov2011.docx

* If you are currently running Enterprise as a Server + CAL and you upgrade to SQL 2012 through Software Assurance, you can keep Server + CAL model, providing you don’t exceed 20 cores.

Master Data Services Training in the UK

Jeremy Kashel — Tue, 01 Nov 2011 23:33:00 GMT

This is just a quick post to announce a range of SQL Server training courses, organised (and in some cases delivered) by Chris Webb. To start off there’s a SQL Server course delivered by Christian Bolton in December, followed by an Analysis Services course delivered by Chris in February. I’ll be delivering a Master Data Services course in February, before Chris delivers an MDX course in March.

The details for all the courses are:

SQL Server Internals and Troubleshooting Workshop - Christian Bolton – 6th – 7th December 2011

The Advanced Troubleshooting Workshop for SQL Server 2005, 2008 and R2 provides attendees with SQL Server internals knowledge, practical troubleshooting skills and a proven methodical approach to problem solving. The workshop will enable attendees to tackle complex SQL Server problems with confidence.

Full details and registration here.

Real World Cube Design and Performance Tuning with Analysis Services – Chris Webb – February 2012

A two day course that takes real world experience in showing you how to build a best practice Analysis Services cube, covering design issues such as data warehouse design and complex cube modelling. Day two then covers performance optimisation for Analysis Services, including MDX optimisation and cube processing.

Full details and registration here.

Introduction to Master Data Services with Jeremy Kashel – February 2012

An end to end look inside Master Data Services, this full day course will begin with a synopsis of Master Data Management before moving on to an overview of Microsoft SQL Server 2008 R2 Master Data Services (MDS). The remainder of the course will cover the major MDS topics, such as modelling and business rules, which will include a number of practical exercises.

More details and registration for here.

Introduction to MDX with Chris Webb – March 2012

The Introduction to MDX course aims to take you from the point of being a complete beginner with no previous knowledge of MDX up to the point where you can write 90% of the MDX calculations and queries you’ll ever need to write. The three day course covers the basics, such as sets, tuples, members to more advanced concepts such as scoped assignments and performance tuning.

Full details and registration here.

HP Business Decision Appliance–PowerPivot in a box!

Calvin Ferns — Thu, 27 Oct 2011 08:32:31 GMT

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

MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook - Book Review

Jeremy Kashel — Thu, 13 Oct 2011 16:51:00 GMT

As you may have already seen, Packt have released a new MDX book, namely MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli.

The book is written as cookbook, meaning that you can choose the individual ‘recipes’ that apply to whatever problem you’re trying to solve. Each recipe starts off with a section called ‘Getting Ready’, essentially giving you the base query to use, before moving onto ‘How to do it', which covers the main instructions for the recipe. There are then further sections, which explain how the example works and also suggest other functions/concepts that you may want to consider. This sort of cookbook style makes it really easy to follow, each recipe is displayed very clearly in the book.

A wide range of MDX problems are covered, from time calculations to context-aware calculations. Not every piece of the MDX functionality is covered, which is to be expected, given the size of the book. It also doesn’t cover the usual introduction to members/sets etc that MDX books tend to cover, but it’s clearly stated that having a working knowledge of MDX is a pre-requisite for the book.

I found the copy that I’ve been reading in the Adatis office, but I really do like it, so I’ll definitely be ordering my own copy!

SQL Server can scale

Calvin Ferns — Wed, 21 Sep 2011 17:52:17 GMT

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 )

Master Data Services Kindle Contest

Jeremy Kashel — Tue, 13 Sep 2011 12:55:09 GMT

This is a quick blog post to announce that Adatis are running a contest in conjunction with Packt around Master Data Services. The winner will receive an Amazon Kindle pre-loaded with our Microsoft SQL Server 2008 R2 Master Data Services book.

In order to enter, all you need to do is follow @AdatisBI and copy and paste our eBook on Kindle message and retweet it using your twitter account. The message is:

RT & Follow @AdatisBI for a chance to WIN the #Adatis MDS book pre-loaded on a Kindle ! (http://bit.ly/roogTd)

Please mark your retweet with the hashtag (#), so that we can find and consider your entry. Only one entry per person will be accepted.

You can find out the full details of how to enter, as well as terms and conditions, on the following page.

Good luck!

Master Data Services Excel Add-in

Jeremy Kashel — Thu, 25 Aug 2011 08:24:50 GMT

Master Data Services in SQL Server Denali now includes an Excel add-in that allows MDS users to add and update data that exists within Master Data Services. For those of you that haven’t had a chance to download the latest CTP, this post gives an overview of what’s possible in the Excel Add-in.

Excel Add-in Overview

Once you install the Excel add-in (available here for download) you will see an additional toolbar in the ribbon, as shown below:

At a high level, the following functionality is available within the MDS add-in:

Retrieve members from a master data entity
Add or Update members (including annotations) and publish the changes back to MDS
Create entities in MDS if you have sufficient permission
Run the business rules

I don’t want this post to go on for ever, so today I’m going to focus on retrieving and updating members.

Retrieving MDS Entity Members

The Excel Add-in comes with a sidebar called the Explorer that can be used to connect to an MDS model and retrieve data from an entity within the model. What I quite like is that there is a filter button that allows a user to specify how to filter the data before loading it. In the example below, I’ve connected to the sample customer model, and have filtered the customer entity to only show customers of type 1 and customers from Seattle:

On the same filtering window its possible to choose the attributes to display. Therefore in the above example, by clicking the ‘Load Data’ button, a filtered list of customer members will be shown:

Editing Members

Once the members have been retrieved, editing is just a simple case of typing into the field that you want to change. In the example below I’ve chosen to change the names of two of the customers, which has caused the cells to be highlighted, informing me of the changes that I’ve made:

Domain-based attributes are also picked up nicely by Excel. Not that I imagine it would make too much sense, but it’s possible to change the Sales District North Western US (WUSSL) to Central US (CEUS), for example. Excel handles this by rendering a drop down for this domain attribute:

As it is in Master Data Manager, within Excel it’s also possible to display a domain attribute’s name.

Regardless of the type of attribute that gets changed, the changes will just remain in the Excel sheet until I click the publish button:

Clicking the the publish button will result in being prompted to make an optional annotation for each of the changes:

The annotations, as well as the member updates, will be committed to the Master Data Services database. The end result is that the transactions can be viewed as per 2008 R2 in Master Data Manager, or by right clicking in Excel and choosing ‘View Transactions’.

Summary

The Excel add-in doesn’t contain all the functionality available within Master Data Manager, but provides an interface that clearly Excel-based data stewards will be very comfortable with. It’s also much easier to do bulk updates in the Excel front-end, when compared to the web front-end.

That’s about if for now. There’s more to the Excel add-in, which I’m aiming to cover at SQLBits 9, plus Master Data Manager has been given a complete overhaul in Denali – hopefully I’ll find the time to cover that soon…

Master Data Services Training

Jeremy Kashel — Mon, 22 Aug 2011 08:49:26 GMT

As I’m sure you’ve seen, registration for SQLBits 9 – Query Across the Mersey is now open.

This time around I’ll be running a deep-dive training day with Tim Kent on Master Data Services. This will be a full day of training, showing you how MDS can be used to manage the master data in your organisation. We’re going to start by giving an overview of Master Data Management, before moving on to covering the following MDS topics in detail:

Installing, configuring, and maintaining Master Data Services
Creating and using models
Version management
Business rules and workflow
Importing data into Master Data Services
Integrating with other systems
Security

You can register for the MDS Training day, or one of the other 10 training days, by using the following registration page.

Remember, the early bird discount expires at midnight on Friday this week!

Update – We are running another MDS course in February 2012. Click here for the details.

A Pattern To Load Data to Master Data Services via SSIS–Part 2

Neil Dobner — Thu, 18 Aug 2011 16:26:30 GMT

Introduction

This is the second part of a series of blog posts intended to show a way to load data in to MDS via SSIS. In part 1 we have loaded the staging tables with new members and attributes for several entities. In this blog post we are going to extend the SSIS package with tasks to move the data from the staging tables into the MDS Product model and validate these newly inserted members.

Completing The Solution

We need to move the data from the staging tables into the model. This is carried out by executing the MDS staging sweep process. To achieve this we need to add an Execute SQL Task to the control flow of our package. Rename the task – I’ve called mine ‘SQL – Sweep Stage’ and connect it up to the ‘DFL – Load Staging Tables’ task with a success constraint.

On the General tab set the connection to MasterDataServices and the SQL Statement as follows:

DECLARE @ModelName   NVARCHAR(50) = ? 
DECLARE @UserName    NVARCHAR(50) = ?
DECLARE @User_ID     INT 
DECLARE @Version_ID  INT

SET @User_ID =    (SELECT ID  
                   FROM  mdm.tblUser u 
                   WHERE u.UserName = @UserName ) 

SET @Version_ID = (SELECT MAX(ID)  
                   FROM mdm.viw_SYSTEM_SCHEMA_VERSION  
                   WHERE Model_Name = @ModelName)
                   

EXECUTE mdm.udpStagingSweep @User_ID, @Version_ID, 1

Then add the Parameter mapping as shown below:

That’s all there is to do to get our data into the model. However this process is asynchronous and before we can validate the model we need to know when the staging sweep has finished.

Add a For Loop Container task to the control flow of the package and connect it up to the ‘SQL – Sweep Stage’ task with a success constraint. Rename the task – I’ve called mine ‘FLC – Wait Until Batch Completes’. Add an Execute SQL Task inside the loop container task and rename it. Mine is called ‘SQL – Get Staging Batch Status’. Change the connection to MasterDataServices, change the ResultSet property to ‘Single row’ and then add the following SQL script to the SQLStatement property:

DECLARE @Version_ID INT

SET @Version_ID = (SELECT MAX(ID)  
                   FROM mdm.viw_SYSTEM_SCHEMA_VERSION  
                   WHERE Model_Name = ?)

SELECT TOP    1 Status_ID 
FROM          mdm.tblStgBatch 
WHERE         Version_ID = @Version_ID
ORDER BY      ID DESC

Add the parameter mapping as shown below:

And the Result Set as shown below:

Add three more parameters to the package as shown in the table below:

Next configure the For Loop Properties as shown in the table below:

The InitExpression value sets the @dtMDSLoopTimer to the current time plus the interval set in our @intMDSTimeout variable. The EvalExpression checks if the @strMDSBatchStatus is either not equal to 2 (Success) or the timeout has expired.

The For Loop Container can only succeed if the staging batch is successfully loaded.

Now we can validate the model so add an Execute SQL Task to the control flow and connect it to the ‘FLC – Wait Until Batch Completes’ task with a success constraint. Rename the task - mine is called ‘SQL – Validate Model’. Change the connection to MasterDataServices and the SQLStatement as follows:

DECLARE @ModelName    NVARCHAR(50) = ?
DECLARE @UserName     NVARCHAR(50) = ?
DECLARE @User_ID      INT 
DECLARE @Version_ID   INT
DECLARE @Model_id     INT 


SET @User_ID =        (SELECT ID  
                       FROM  mdm.tblUser u 
                       WHERE u.UserName = @UserName ) 

SET @Version_ID =     (SELECT MAX(ID)  
                       FROM mdm.viw_SYSTEM_SCHEMA_VERSION  
                       WHERE Model_Name = @ModelName)
                   
SET @Model_ID =       (SELECT Model_ID 
                       FROM mdm.viw_SYSTEM_SCHEMA_VERSION 
                       WHERE Model_Name = @ModelName) 


EXECUTE mdm.udpValidateModel @User_ID, @Model_ID, @Version_ID, 1

Set the parameter mapping as follows:

Almost done. Just to finish it off lets add a script task to the control flow and connect it to the ‘FLC – Wait Until Batch Completes’ task with a completion constraint. Change the Evaluation Operation of the constraint to ‘Expression and Constraint’ and set the Expression to ‘@strMDSBatchStatus != 2’. Edit the script and add the following line of code under // TODO: Add your code here:

Dts.Events.FireError(0, "SCR - Fire Error", "MDS Timeout Occurred", string.Empty, 0);

This task will fire an error event if the MDS staging batch does not complete successfully.

The finished package control flow should look similar to the following image:

Execute this package and then check the Product entity in MDS. It should look something like the following:

Looking at the other entities you will see that we have added members to three entities and validated all these new members.

Summary

Over the last two blog posts I have shown a way of automating the loading of data to Master Data Services via SSIS. This pattern can be used to cater for most of your loading requirements.

That’s it, the completed Integration Services project source code and MDS Model can be downloaded from here – (You will need to create a login first).

Your comments are very welcome.

Slides from Avon Information management talk

Calvin Ferns — Thu, 21 Jul 2011 11:42:43 GMT

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

A Pattern To Load Data to Master Data Services via SSIS

Neil Dobner — Thu, 14 Jul 2011 15:37:00 GMT

Part 1 Introduction

Loading new members to a MDS entity will be a common requirement in all MDS implementations. In these blog posts I am going to walk you through building an SSIS package that performs the following processes:

Load new members and attributes to several entities via staging tables
Validate the MDS model that contains the entities

In part one we will load the MDS staging tables ready to take our new members and attributes into our MDS model. For a thorough understanding of the staging process in MDS please see the Master Data Services Team blog post on Importing Data by Using the Staging Process.

A pre-requisite is to have the AdventureWorks2008R2 database samples installed on the same instance of SQL Server as Master Data Services.

In MDS I have created a model named ‘Product’ with an entity of the same name. The product entity has the following attributes which are set to the default type and length unless specified:

Name
Code
Model (Domain Attribute)
Culture (Domain Attribute)
Description (Text, 500)

We are going to load this entity with Product data from the AdventureWorks2008R2 database using a SSIS package.

In addition to this there are two further entities in the Product model:

Culture
Model

These entities have just the code and name attributes and are set to the default type and length.

The MDS model and Integration Services project source code can be downloaded from here – (You will need to create a login first).

Building The Solution

OK enough of the intro let’s get on and build the package.

Start a new Visual Studio Integration Services Project and save the default package to a more suitable name. I’ve called mine ‘LoadMDS.dtsx’.

Create the following connection managers as shown below remembering to replace the Server and MDS database names.

Rename the connection managers to ‘AdventureWorks’ and ‘MasterDataServices’ respectively.

Now we need to create some variables so go ahead and create the variables shown in the table below:

We are now ready to put our first task into our package. This task will optionally clear the staging tables of all successfully loaded members, attributes and relationships prior to loading, based on the value of the blnClearStage parameter.

Add an Execute SQL Task to the control flow of your package and rename it - I’ve called mine ‘SQL – Clear Staging Tables’.

On the General tab set the connection to MasterDataServices and the SQL Statement as follows:

DECLARE @ModelName NVARCHAR(50) = ? 
DECLARE @UserName  NVARCHAR(50) = ? 
DECLARE @User_ID   INT

SET @User_ID =    (SELECT ID  
                   FROM  mdm.tblUser u 
                   WHERE u.UserName = @UserName ) 

IF ? = 1 
    EXECUTE mdm.udpStagingClear @User_ID, 4, 1, @ModelName, DEFAULT 
ELSE 
    SELECT 1 AS A

On the Parameter Mapping tab add the variables exactly as shown below:

Add a Data Flow task to the control flow of the package and connect it to the ‘SQL – Clear Staging Tables’ task with a success constraint. Rename the task to ‘DFL – Load Staging Tables’.

Add three further variables to our package as follows:

In the data flow of our package add an OLEDB data source task, set the connection to AdventureWorks and the Data Access Mode to SQL Command. Add the following SQL to the SQL command text window:

SELECT 
    CAST(p.ProductID AS VARCHAR(10)) + pmx.CultureID            AS ProductCode
    ,p.Name            
    ,p.ProductModelID
    ,pm.Name                                                    AS ProductModelName
    ,pmx.CultureID
    ,c.Name                                                     AS CultureName
    ,pd.Description
FROM Production.Product                                         p 
  INNER JOIN Production.ProductModel                            pm 
    ON p.ProductModelID = pm.ProductModelID 
  INNER JOIN Production.ProductModelProductDescriptionCulture   pmx 
    ON pm.ProductModelID = pmx.ProductModelID
  INNER JOIN Production.ProductDescription                      pd 
    ON pmx.ProductDescriptionID = pd.ProductDescriptionID
  INNER JOIN Production.Culture                                 c
    ON pmx.CultureID = c.CultureID

Don’t worry if the formatting turns ugly, that’s just what happens. Press the Preview button and you will see that this query will return us the following columns to our data flow:

ProductCode
Name
ProductModelID
ProductModelName
CultureID
CultureName
Description

We need two more columns in our data flow and to get them we will use a Derived Column transformation task so drag one on to the data flow from the toolbox and connect it up to the data source. Add the columns as shown in the image below:

Next the data flow needs to be duplicated into multiple streams so that the different members and attributes can be loaded to the staging tables. This is achieved by adding a Multicast transformation task to our data flow. This task does not require any configuration. There will be six outputs from the Multicast task and these will be used to load the following:

Product Members
Model Members
Culture Members
Product Model Attributes
Product Culture Attributes
Product Description Attributes

Each of these outputs needs to be tailored as to whether they will be loading a member or an attribute and also which member or attribute they are loading. Add six Derived Column transformation tasks to the data flow and connect them to the Multicast transformation. At this point our data flow should look similar to the following:

For each of the Derived Column transformations add the additional columns as specified below:

OK now we have got all the information we need in our data flows to start loading to the staging tables but before we do that there is one more thing to do. As we are loading new members to the Model and Culture entities as well as Product we need to ensure that we have only distinct values for our member codes to prevent staging errors. To achieve this we add and connect Aggregate transformation shapes to the data flows underneath the ‘Add Culture Member Information’ and ‘Add Model Member Information’ shapes. The images below show how to configure these aggregate transformation shapes:

Group By Culture Group By Model

We are now ready to load the data to the MDS staging tables. Add six OLE DB destination shapes to the dataflow. Three of the destinations will be to load new entity members and the other three will be to load attributes for these new members. Configure the Connection Manager properties of the destinations as follows:

Members Attributes

Connect the first destination shape to the ‘Add Product Member Information’ shape and configure it as a member destination. Click the Mappings tab and set the Input and Destination column mappings as shown below:

Connect the second destination shape to the ‘Group By Culture’ shape and configure it as a Member destination. The column mappings will be the same as above except for the MemberName and MemberCode columns and these will be set to CultureName and CultureID respectively.

Connect the third destination shape to the ‘Group By Model’ shape and configure it as a Member destination. The column mappings will be the same as above except for the MemberName and MemberCode columns and these will be set to ProductModelName and ProductModelID respectively.

Connect the fourth destination shape to the ‘Add Culture Attribute Information’ shape and configure it as an Attribute destination. The column mappings will be as follows:

Configure the next two destinations as Attribute destinations and map the columns as the other Attribute destination replacing the AttributeValue mapping with ProductModelID and Description respectively. Now our completed dataflow should look similar to the following:

If you execute the package you will see that we have inserted 1764 Product member rows, 6 Culture member rows and 118 Model member rows into the mdm.tblStgMember table and 1764 attribute rows for each of the Culture, Model and Description attributes into the mdm.tblStgMemberAttribute table in your MDS database. It is worth noting that the data has now been staged only and we will not see it in our MDS entities yet.

OK that’s as far as we are going to go in part one. In part two we will extend the package to move the data from the staging tables into the MDS model and validate the newly inserted data.

Data Quality Services

Jeremy Kashel — Wed, 13 Jul 2011 12:40:17 GMT

As I’m sure you’ve heard, CTP3 of SQL Server Denali was released yesterday, and can be downloaded here. Denali includes Data Quality Services (DQS), Microsoft’s new data cleansing and matching component that’s based on the Zoomix acquisition that occurred a couple of years back. Data Quality Services didn’t make it into the first CTP, but is now available, so I though it would be worth a quick blog post.

Installing Data Quality Services

Data Quality Services is an option in the main install, but when you go to run the Data Quality Client, you’ll get a message stating that DQS is not installed. As far as I can tell, DQS needs to be installed manually, by running the DQSInstaller.exe, which you can find in the SQL Server Binn directory. This will create two SQL Server databases:

Data Quality Client

Once DQS is configured, you’ll be in a position to use the Data Quality Client, which is a windows application, available in 32 or 64 bit. Once you connect, you’ll get the following screen:

The knowledge base is the key to how DQS works, being utilised to determine the data quality of your source data. You can create your own knowledge base, using your own data, or even cloud-based data. For this example, I’m going to use the built in knowledge base called DQS Data.

As an example, I’ve created some data that I want to cleanse. It’s adventure works country data that I’ve put into a separate table that I’ve called dbo.Country.

My task now is is to clean this data, which I can do by creating a data quality project:

I’ve called my project Countries, and I’ve picked the built-in DQS Data knowledge base, which I noticed contains reference data for countries. The activity that I’ve selected is Cleansing, and then I’ve clicked Create.

DQS will then prompt for mapping the source data to one of the domains in the Knowledge Base. Here I’ve mapping my country name to the Country domain from the knowledge base:

After clicking the Start button on the next screen, the cleaning process starts, which gives the following results:

I put a couple of typos into my source data, such as changing Algeria to ‘Algerian’ and Albania to ‘Albana’. These were picked up by DQS, along with a few others, but a user has the opportunity to approve or reject, via the radio buttons shown above. I chose to approve the first two, and then clicked next. In the final screen, DQS allows you to output the cleaned data to a separate table, as shown below:

If you go into the table that DQS has created, you will see that there is a Name_Status column, which holds the cleaning status on a per record basis:

This can be used to update the source data, if required, and therefore address the data quality issues.

Summary

This has been a quick introduction to cleaning data with Data Quality Services. There’s plenty more to look at, and I hope to find time to do so at some point - In particular I’m keen to take a look at how DQS works with Master Data Services. I’ve noticed that there’s also a new MS DQS blog (http://blogs.msdn.com/b/dqs/) – I’m sure that will be a great resource for DQS learning.

PowerPivot–Simple Custom Rollup

Shaun Ryan — Tue, 05 Jul 2011 15:08:12 GMT

Introduction

I have been having a bit of hands on with PowerPivot just lately and one of the aspects of PowerPivot that I think is extremely powerful is that it provides users with the ability to mash up their own data in Excel with data from a structured SQL Server database. More specifically allowing users to create and control how granular attributes of a dimension might rollup up into a hierarchy is a valuable thing, for example an organisational structure or an accounts hierarchy. Users do need to be quite good with data modelling and DAX to leverage PowerPivot and time will tell on that one. However, with that in mind I’m going to create a user controlled custom rollup on an accounts dimension using only very basic data modelling and DAX concepts.

Essentially within an accounting solution you might want to avoid storing numbers in the context of positive (+ve) and negative (-ve) since in accounting terms either the business owns it or it doesn’t i.e. it’s either an asset or a liability. Also things come into the business and things go out i.e. they purchase supplies and sell goods and services. None of the accounts themselves are either +ve or –ve since in accounting we deal with either credits or debits. Depending on what aspect of the business you’re evaluating and how you’re presenting the figures an account may be considered as in credit for one context but in debit for another. For example the Closing Balance of Stock is considered a Credit in the P&L but a Debit in the Balance Sheet.

In BI data modelling terms we deal with this by associating the aggregation operator with the reporting dimension hierarchy rather than the number itself thus when the measure is combined with the hierarchy it is aggregated within the context of the hierarchy. More specifically if you’re familiar with SQL Server Analysis Services (SSAS) we do this using a rollup operator that is stored on the accounts dimension.

This blog is a simple PowerPivot design pattern to allow business users to create their own custom rollups on an accounts dimension and trial balance fact table from SQL Server.

Solution

I always like to present the final solution before going into the specifics of the design since knowing the final output tends to make design details a bit easier to digest. So here’s what we’re aiming for:

So here we can see I’ve got a P&L hierarchy that rolls up the accounts through Gross Profit and Expenses and calculates the Profit. The accounts and figures have all been take from a SQL Server database however which accounts and how they have been rolled up into Cost of Goods Sold, Sales, Gross Profit, Expenses and Net Profit has all been created and controlled by the Excel user. The P&L roll up is created from the following table in the Excel file that is linked into the PowerPivot model:

So in this table we can see the accounts mapping to relevant parent P&L members and a rollup operator that indicates how the account should be evaluated. The Rollup operators should look familiar if you’re from a SSAS background like me since the symbols are the same. + is for sum, – is subtract and ~ is to exclude. Notice there are many accounts that are not used here since they are not included in the P&L and as such have no parents defined either. A different rollup application might have the numbers excluded from the rollup but still have them included in the hierarchy.

Implementation

The underlying SQL Database contains an Accounts dimension, Date dimension and a fact table containing account Balances. The simple star schema is as follows:

From the diagram we can see that the Account dimension is simply a list of accounts and there is no hierarchy. FactBalance contains the account trial balances which are all physically positive numbers which can conceptually be either summed or subtracted. I’ve included a very simple Date dimension as usual since it’s always good to see how a solution works with more than 1 dimension in play to test and understand what is going on inside the software without making it too complex.

The next step is to pull these 3 tables into Excel 2010 using the PowerPivot add-in. I won’t go into how this is done in detail since there’s loads on the web about getting started with PowerPivot and I’m assuming if you’re looking to do custom rollup then you’ve already made a start with the basics. This is a pretty good book to get your hands on since it covers the basics and some more advanced topics you can get your teeth into further down the road: MS PowerPivot for Excel 2010 (Marco and Alberto)

Below is the Accounts dimension and Balance fact table that I’ve pulled into PowerPivot and renamed for usability.

The next step is to go to the Balance table tab and relate for Account_Key to the Account_Key in the Account table and Date_Key to the Date_Key in the Date table. If your database had these relationships when you imported the tables then PowerPivot will have created them automatically.

So, now we have our PowerPivot model we’re going to create a table in Excel to rollup the Accounts dimension. Enter the following data into spread sheet in the workbook.

With Cell A1 selected click the Link Table icon on the Excel PowerPivot tab.

This will import the data into the PowerPivot model linked directly to the data in the Excel sheet. Rename the new table we’ve just imported to something useful and relate the Account dimension to it using the Account Code and it should look like as follows:

So now we have all the data nicely imported into PowerPivot and related together we’ll apply the custom rollup. To do this I’m going to use the way that SSAS actually applies the operators under the covers (I believe but am sure someone will correct me if I’m wrong). Essentially + operator multiples the fact data by +1, – multiplies the fact data by –1 and ~ multiplies the fact by 0 or null to effectively yield null. After the operator has been multiplied which adjusts the sign of the data or nulls it out we leave it to PowerPivot to simply sum the result to give the answer. At this point you’re probably right in thinking I could’ve just used +1, –1 and 0 instead of +, – and ~ however I just wanted it to be consistent with what we’re (or I’m) used to in SSAS.

In the Budget table we’re going to create a calculated column called PL Operator that uses the PowerPivot relationships we’ve defined to pull the operator down into the Budget table. The simple DAX formula I’ve used can be seen in the next screen capture:

Once we have the operator we can create another calculated column to create the PL Amount column that applies the operator by multiplying PL Operator with the Balance as shown below.

So we can see the PL Amount column now has the rollup operator applied. The only thing left to do is review what columns you want to make visible in the model and drop a pivot table onto a spread sheet and have a look. We could’ve just used 1 calculated column to achieve what we needed to do, I broke it down into 2 just for presentation purposes. Have a play at modifying the Excel based data rollups and refreshing the data model to see how quick and easy it is to manipulate the rollup.

Conclusion

This is a really simple way for users to consume data from a database and apply a very simple custom rollup. I really like the simplicity of it and the fact as a user I can just edit my rollups in my sheet and refresh the data and adjust how the accounts roll up. Also you might have noticed there are trial balance figures for the accounts in my data that would be needed to create a balance sheet and so we could progress this further by having another roll up table to create a Balance Sheet.

I’m going to have a play around a bit more and see if it’s possible to create more complex rollups and perhaps apply different rollup operators for different columns instead of just at the grain of the account. This is where I suspect things will become somewhat more complicated very quickly! Coming from a SSAS background I find it quite useful to take SSAS modelling concepts and challenge my self and PowerPivot to achieve the same end. PowerPivot modelling is a different way of thinking and whilst it may not do what SSAS does out of the box I’m convinced where there is a will there is a way, or maybe not.