Book Review: Microsoft SQL Server 2008 R2 Master Data Services

A new MDS book is available from Packt Publishing: Microsoft SQL Server 2008 R2 Master Data Services.  The authors are from Adatis, a Microsoft Partner in the UK.  Jeremy Kashel (blog) authored 8 of the 10 chapters. Tim Kent (blog) and Martyn Bullerwell (blog) each contributed a chapter.

First Impressions

The first chapter does a terrific job of introducing terms and concepts such as entity, attribute, members, etc.  There’s a nice overview of master data management.  As you’d expect, the book consistently serves as a good reference for explaining screens, buttons, and functionality.  At the end of each chapter there’s a summary of key points which I liked.

You Need This Book If…

Although the first 5 chapters are good, it was starting at Chapter 6 is where the book really started to stand out for me.  I found the in-depth coverage & step by step instructions for these areas to be particularly impressive:

• Using SSIS to import data into MDS (chapter 6)
• Integrating MDS Business Rules with SharePoint Workflow (chapters 7 and 9)
• BizTalk Integration (chapter 8)
• Using the API (chapter 9)

I Wish, I Wish

Not every book can contain everything any reader wants.  The things I really wanted to see more of were:

• Additional guidance on how to decide when to use a Derived vs. Explicit Hierarchy.  The terms were defined, but I’m not certain a reader new to the technology would have gleaned quite enough information to make an informed decision.  (Conversely, practical uses for Versions were listed on page 124 – that was terrific & very helpful.  I wanted something like that related to the hierarchies.)  In all fairness, I just dealt with hierarchy selection at a client site so I had it on my mind.
• Deployment and migration information & tips (ex: what the deployment package does & does not contain).
• More real life implementation experiences, troubleshooting (ex: troubleshooting the installation) and gotchas (ex: with imports and exports).

Overall, I really liked it.  The authors clearly made a big time investment & it shows with a high quality book.  My MDS knowledge is more well-rounded now, which makes me a happy girl.

Thanks to Packt Publishing for providing me with a complimentary e-book.

Repeating Column Headers on Every Page in SSRS Doesn’t Work! …Or Does It?

Overview: A few tips for getting the column headings to display at the top of each page of a SQL Server Reporting Services (SSRS) report.  SQL Server 2008 R2 was used for all examples.

Using the Advanced Properties

This entry is to expand on my previous blog about Repeating Column Headings which discusses the use of Advanced Tablix Member Properties to repeat column headings on each page.  If you haven’t used the “RepeatOnNewPage” property too much, it can seem like sometimes it works & sometimes it doesn’t.  It can definitely be a bit frustrating.  

Before we go into a few examples, I want to mention the Tablix Properties.  These are great when working with a Matrix; however, they have no affect with a regular Table data region. 

    

Rather than using the above Tablix Properties, since we have a Table we’re going to be using the Advanced Tablix Member Properties.  The Advanced Tablix Member Properties become available when you click the little arrow on the right side of the grouping pane.

    

    

Case 1:  No Row Groups – Column Headings in a Header Row – Works!

This report has one Details group, but no parent groups.  Notice the column headings are located in a header row of the table.  The RepeatOnNewPage property works perfectly in this type of layout (i.e., no row groups).

    

Case 2:  One Row Group – Column Headings in a Header Row – Repeating Does NOT Work

In this layout I moved the Product to be a parent group of the Details.  I left the column headings like they were in the previous example:  in a header row of the table.  Although I set the same advanced tablix member properties, the column headings do NOT repeat on the following page.

Why, you ask?  Read on to Case 3.

    

Case 3:  One Row Group – Column Headings Within the Group – Works…Maybe

This layout moves the column headings to be within the row grouping (see how they are within the bracketed rows, rather than one row on top (like they were in Case 2). 

    

This technique works, but since the headings repeat within every single group, it might or might not work for your real life situation.

    

Case 4:  Using a Fake Group – Row Headings Within the Group – Works Great!

This last layout improves on Case 3 by creating a fake group.  This fake group is so we can keep the Column Headings within a row group, yet not have them repeat for every product that changes.

    

The group is set up like this; it really doesn’t group on anything:

    

The column headings now only appear once, at the top of every page.  Perfect!

    

Finding More Information

There’s a key concept discussed at this MSDN article:  How to Display Row and Column Headers on Multiple Pages (Reporting Services).  It's this comment that inspired me to use the "fake group" approach discussed in Case 4.

"For a table, the row that contains column names is controlled by the tablix member in a row group. Set tablix member properties in the Properties pane."

Updating the SSRS “Name” Property to Control CSV Column Headings

Overview:  A quick tip about generating proper column names when exporting to a CSV file from SQL Server Reporting Services.

When Is the SSRS Textbox Name Relevant?

When SSRS creates the Name property for a textbox, SSRS tries to be helpful & use the field name.  This could be good, or it could be rather cryptic.  It’s also common to have many textboxes named something like “Textbox1” and “Textbox2.”  Although I like my reports to be tidy, I’ve not been terribly disciplined in the past about cleaning up the textbox names, except in these situations:

• When defining Visibility or Interactive Sorting.  Ex:  Detail rows are set to Show when textbox named “Division” is clicked.  I blogged about Visibility Settings here.
• Usage of the ReportItems collection.  Ex: Referring to the value in textbox “Quota_For_Month” in an expression within another textbox:  =Sum(ReportItems("Quota_For_Month").Value).  You can check out few examples of using the ReportItems collection here.

As of today, I have a new situation to add to the list:

• When end users export the report to CSV format.

CSV Format Uses “Name” Property as Column Headers

By default, exporting to a CSV (comma separated values) format will utilize the textbox names for each column heading.  Rather than seeing useless column headings like this....

    

....We’d rather see well-named column headings such as this:

    

Updating the Textbox Name Property

The “Name” property can be updated within the Properties Pane:

    

Or, it can be updated within the Property Pages:

    

Taking just a bit of time to update each Name property provides a really nice benefit for end users, should they use CSV export functionality. The end user says to me: “I have to update the column headings each time I export.” Ouch! That hurts! So glad he mentioned it so I could vastly improve the end user experience with such a simple change.

Delivering Alternate Hierarchies for Reporting

Overview:  This post discusses the approach we used in delivering alternate hierarchies for financial reporting within a SQL Server 2008 R2 environment.  Part 1:  the solution, which is a valid alternative if you don’t have unary operators.  Part 2:  workaround we implemented to deal with a unary operator issue.

To Give Credit Where Credit is Due:  Leo Furlong of Intellinet was the architect of this design.  I helped implement it, but he was the brains.

Part 1:  DW and SSAS Solution for Alternate Hierarchies

Business Requirements

The requirements presented to us included: 

1.  For financial reporting, each account may reside in one or more hierarchies for reporting.  Example:  the Sales Returns & Allowances account participates in the GAAP Reporting, Tax Reporting, and Internal Reporting Hierarchies; it does not participate in the Segment Reporting Hierarchy.

2.  For the same account, a unary operator (+/-) may differ between hierarchies.  Example:  in the Internal Reporting Hierarchy, the Discounts account is subtracted from its parent, whereas in the Segment Reporting hierarchy it is added to its parent.

3.  The rollup levels are ragged (unbalanced).  Example:  The GAAP Reporting hierarchy has 6 levels, whereas the Internal Reporting Hierarchy has 11 levels.

4.  Sorting of the members within each level of the hierarchies needs to be customized (i.e., alphabetical is not sufficient).

Sample of the results one hierarchy would be intended to produce:

     

The Solution

Relational DW

From a relational data warehouse perspective, we implemented a many-to-many bridge table to handle the relationship between accounts and reporting hierarchies. 

Dim Account:  Grain is one row per distinct account.  This table has the direct relationship to the fact tables.

Dim Account Reporting Hierarchies:  Grain is one row per account + each hierarchy the account belongs to. 

• Each row has a recursive relationship to its parent row.
• Contains all account attribute fields, including the unary operator (+/-) field. 
• Each field has a sort value (retrieved from MDS – discussed next).

Following is a simplified version of the data model:

       

Master Data Services

Both Account dimensions are populated (via SSIS ETL) from a Master Data Services model. 

Each reporting hierarchy was set up in MDS as an Explicit Hierarchy, which allowed for the hierarchies to be ragged.  An Explicit Hierarchy also permits drag & drop ordering of the members within each level (whereas a Derived Hierarchy does not).

Analysis Services

Dim Account: Displays just a few attributes (such as Account Type, Account Name & Number, etc).  Because it’s directly related to the fact table, it has a “regular” relationship to the measure group.

Dim Account Reporting Hierarchies:  The only visible attribute is the the parent-child hierarchy which serves up the reporting levels.  The unary operator was delivered using the functionality built into a parent/child dimension in SSAS.  There’s a few other attributes, set to be hidden, which are exposed through member properties.  This dimension has a “many to many” relationship to the measure group, through the bridge measure group, as shown here:

       

Initially it seemed like this solution would work really well. It would have worked beautifully if we had no unary operator issues to contend with.

The remainder of this discussion deals with a workaround we had to implement due to a unary operator issue.  If you don’t have unary operator data, the above solution is a valid option to consider.

Part 2: Dealing with Unary Operator Issues

The Unary Operator Problem We Ran Into

The unary operator did not function properly when “subtraction” accounts were involved.  What we found consistently was the immediate parent of any subtraction accounts aggregated properly (ex: Level 2-C below); however, the parent of the parent (ex: Level 1 below) did not.  The difference was always 2x the total of the subtraction account children.  Aggregations where the children were always addition worked just fine.

Example:

      

After doing some research, we narrowed down the problem to being related to the many-to-many relationship.  It’s currently documented in this Connect issue.  Since only 10 people have logged that they can reproduce the bug as of mid-2011, I’m not betting Microsoft finds it a huge priority to get it fixed.  Hence, a workaround was born…

Workaround to Handle Unary Operators

The immediate thing we wanted to do was eliminate the many-to-many relationship and convert it to a regular relationship.  The goal was: if we can eliminate the many-to-many relationship, all other aspects of our solution would likely work as designed.  Therefore, we did the following:

1. Left the existing fact table as-is (i.e., it still is the “primary” fact table in the DW).  Leave the existing dimensions as-is.
2. Implemented a secondary fact table (ex: Fact Finance Hierarchies in the screen shot below) which flattened out each fact record with the hierarchy it belongs to.  Caution:  this does create duplicate fact records.  This secondary fact table is populated at the end of the ETL process.  These records are created physically in the ETL process at the client’s request; a very valid alternative would be to create this flattened table using a view.
3. In the SSAS Data Source View, remove the existing fact table. Replace it with the secondary fact table which has one record for every hierarchy record.
4. Change the relationship to be Regular; remove the bridge measure group.

Following is a simplified version of the data model after the workaround, from the perspective of the SSAS data source view:

           

After this implementation, the unary operator aggregates worked properly when we browsed the cube.  Although there’s a number of ways to handle it, we found this to be the most straightforward & easy to maintain for our client.

Comments & alternative ideas are welcomed!

New Master Data Services Book Released

There's a new MDS book released by Packt Publishing:  Microsoft SQL Server 2008 R2 Master Data Services.

The authors are from Adatis, a Microsoft Partner in the UK.  Jeremy Kashel (blog) authored 8 of the 10 chapters.  Tim Kent (blog) and Martyn Bullerwell (blog) each contributed a chapter.

Packt Publishing has been kind enough to provide me with a complimentary e-book.  I'll be reading the book & posting a book review soon.

SSAS Processing During the Development Phase

Overview:  A quick tip about using the Object Explorer Details within SSMS for more processing control of a SQL Server Analysis Services database which is actively under development.

The BIDS Environment

When you’re working in an SSAS project in BIDS, the “Default” Processing Option is selected by default.  The other two options are “Do Not Process” and “Full.”  You can’t control the order items are processed, nor how individual items are processed.

    

Have you ever deployed & processed your SSAS changes & received an error that you know full well isn’t a valid error – something that’s only being returned because, for instance, one dimension needs to be fully processed before the measure group? 

That’s because of the “Default” processing setting shown above – sometimes you just need more finite control over the order of processing & the type of processing.  At this point of development you probably don’t have your SSIS environment quite ready yet – and the choices you need when you reach production aren’t the same as development – so here’s a quick tip:  Use SSMS for processing instead of BIDS.

Using BIDS To Process an SSAS Database Actively Under Development

First, set the Processing Option in BIDS to Do Not Process.  Then, when you deploy you really only deploy.

    

Within SSMS, connect to the Analysis Services database.

    

After you’ve connected, view the Object Explorer Details. 

    

Using the Object Explorer Details, first browse to the Dimensions.  Multi-select the dimensions you wish to process, right-click and choose Process.

    

You are then presented with a dialog box with the various Processing Options.  For instance, if you made quite a few project changes for a particular dimension, you could choose a Process Full on one dimension but not necessarily all of the dimensions.  Process Update is the default.  This granular level of control isn’t available in the BIDS environment.

    

When the dimensions have completed, you can then process the Cube (or individual Measure Groups) itself.  Process Full is the default.

    

And that’s it!  This technique isn’t always necessary.  For minor changes, I still let the default processing occur when I deploy from BIDS.  By having more granular control over the processing when you make relatively major changes to the SSAS project, you might eliminate some errors received during the development cycle – thus saving time chasing down something that’s a non-issue. Always a good thing in my book.

Finding More Information

MSDN:  Managing Analysis Services Using SQL Server Management Studio