Sorting Descending in Analysis Services for a Date Dimension: Don't Do It!

Why To Not Sort Date Dimension Descending

Now that I am wiser, my recommendation is to leave your date dimension in ascending order.  Within a day of implementing this technique to sort descending, we found a couple of time-oriented issues within the development environment this had been deployed to.  Here are some reasons not to sort your date dimension descending:

• Unintended consequences on time calculations. 
• Effect on named sets (although the order could certainly be modified).
• Appearance on charts & reports.  Although users would like to see drop-downs list in descending order, they probably don't always want to see their data displayed that way. 

Live and learn.  Thanks for reading.

- Melissa  10/3/2011

-----------------------------------

The Original Blog Entry:

Overview:  This entry describes how I fulfilled a customer requirement to present the Date attributes & hierarchies in descending order, rather than ascending.  This technique utilizes individual sort fields for each attribute.  Each sort field contains the same content as the key, but is multiplied by * -1 to reverse the order.

Level:  201 (this assumes some SSAS knowledge, as all SSAS steps are not discussed in detail).

Analysis Services Order By Options

Following is a screen shot of how my customer wanted to see their Calendar Date Hierarchy displayed:

     

The good news?  Analysis Services attributes have an OrderBy property, which can sort by the Key or Name field of the attribute, or, alternatively, the Key or Name field of a related attribute.

The bad news?  This OrderBy property works in ascending order only.  There is not a descending option, so we need to provide an explicit field to SSAS.

Steps to Deliver Descending Sort Functionality

The steps I used include the following:

1. Relational DB:  Alter the Date dimension in the data warehouse structure to add the integer sort fields.
2. Relational DB:  Alter the stored procedure which builds the Date dimension so that it populates the new sort fields.
3. SSAS:  Refresh the DSV to detect the new relational fields.
4. SSAS:  Add the new sort fields to the Date dimension (hidden to end users).
5. SSAS:  Update the attribute relationships in the Date dimension.
6. SSAS:  Update the OrderBy property for each attribute.
7. SSAS:  Deploy and test.

The rest of this entry elaborates on some of these steps.

Sort Fields in the Data Warehouse Date Dimension

In order to be able to sort every attribute (and every hierarchy) descending, we need a sort field that goes with every attribute.

Using the screen shot below, let’s focus on the “Quarter of Year” attribute.  It contains:

• Calendar_quarter_year_nr:  Key property in SSAS
• Calendar_quarter_year_cd:  Name property in SSAS
• Calendar_quarter_year_sort:  OrderBy property in SSAS

Note the sort field is nothing more than a reversal of the number (_nr) field.   Sample from Dim_Date:

    

Stored Procedure which Builds the Date Dimension

In order to populate Dim_Date, we use a stored procedure that uses a variety of date functions & variables.  The stored proc is is where *-1 logic is used to populate each sort field.  We end up with the same contents as each Number (_nr) field, but the sort field is negative (reversed). 

Sample from the Build_Dim_Date stored proc:

     

Attribute Relationships in SSAS

After you have refreshed the SSAS DSV for the new Dim_Date sort fields, and after you have added each sort field (hidden) to the SSAS Date dimension, you need to update the attribute relationships.  The attribute relationships must in place before the OrderBy property can be updated.

Sample attribute relationships:

    

As you can see in the above screen shot, having a sort field for each individual attribute expands the attribute relationships pane considerably.  However, the relationships are necessary to associate each individual sort field to the field that’s being displayed.  Only related fields (or the field itself) can be used in the OrderBy property.

OrderBy Property in SSAS

The last thing to do is update the OrderBy property for each attribute.  The following screen shot shows our “Quarter of Year” attribute.

    

Note that the OrderBy is done on the AttributeKey – this means “don’t use the key of my Calendar_quarter_year_nr field; instead, use the key of this related field.”  In my case, the way the stored proc is written, the integer number fields are all set up to sort nicely so we we can use Key – that’s not always true.  In many other dimensions, sorting by the key won’t work at all & you need to use Name instead.

A Few Final Words

I typically try to implement as many fields in the underlying data warehouse structure as possible; however, if you only have one or two fields to be sorted descending, another viable alternative is to create a named calculation in the SSAS DSV.

The beauty of this technique is that it’s just so darn simple and it’s quick to implement.  The * -1 technique worked for me with the Date dimension because I had integer fields for each of my Number (_nr) fields. 

Recap of SQL Saturday #89 in Atlanta

Thanks to everyone who attended my session "Dashboards...How to Choose Which MSBI Tool" at SQL Saturday #89 in Atlanta this weekend.  The materials are posted on my Presentations page.  The audience was fun & talkative.  I only had one...err, uhh...minor incident while hurling candy.  The facility at Georgia State University was terrific, as you can tell from this pic from my session:

SQL Saturdays are always so much fun - filled with learning & seeing people or meeting new people.  Seeing The Datachix is always a highlight...here's a pic from the after-party:

Left to right:  Me, Audrey Hammonds (@Datachix2), Julie Smith (@Datachix1)

In addition to catching up with a few folks, I enjoyed meeting a few people like Teo Lachev, Aaron Nelson, Mike Walsh, Jim Christopher, Nick Cain, Grant Fritchey, and Jen Underwood.  My favorite session was probably Adam Jorgenson's - he is one funny guy.

The organizers gave gals shirts that say "data model."  Dudes got shirts that say "I work with models."  Cute, huh?  From what I could tell, the organizers did a mightily terrific job. 

It was a great day.  I sure love our SQL Community.

Resolving the "Cannot connect to configuration database" with Denali CTP3 Virtual Machine

A Hyper-V virtual machine has been made available by Microsoft which contains SQL Server Denali CTP3 + SharePoint 2010 + Office 2010.  I downloaded each part, extracted them, imported my VM, and launched it for the first time with great anticipation.

Then things came to a screeching halt:  Cannot connect to the configuration database.  This message was displayed when I tried to launch the "SQL Server - Home" link on its desktop.  Central Admin had the same error.

To resolve:  go to Services (Start > Administrative Tools > Services) and start the SQL Server service for the POWERPIVOT instance.  Although it's set to be automatic, it's apparently not.

Also, I found I needed to restart the VM twice.  If starting the SQL services doesn't work for you & you've only rebooted once, try one more reboot + starting of the SQL service.  That sequence of events worked for me.

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.