Archive

Archive for the ‘SSAS’ Category

Role Playing Dimensions or Role Dimension?

Recently on the SSAS Forums, there was a question posted which was titled Dimension 1 or Dimension 2. The poster eventually answered their own question. I did not particularly like their proposed solution, decided to reply back with a response, but then realized that an alternate solution would be a better topic for a blog post than a forum response. So here goes that response. The question being asked is a more general dimensional modeling topic than the specific business problem described.

In a nutshell the question being asked was the following: Given that a Country can participate in a business process in two different roles, should the Country dimension be modeled as a single dimension (i.e. Country) or as two role-playing dimensions (i.e. Primary Country and Referer Country)? Additionally, their users needed to be able to query across both dimensions independently where a question could be answered that included contributions from both UK in the Primary Role OR France in the Referer Role. The OR is the important criteria to the solution; otherwise, use a role-playing dimension.

The solution the poster proposed in that post was heavily geared toward a technical approach to resolving the reporting requirement rather than guided by the composition of the business domain. I have to confess. I’m not even sure I could explain how their approach even solved the problem but it did involve creating a dimension directly from the fact table which is almost always leads to a very unscalable solution.

What the Or reporting criteria suggests is that the business domain is composed of a single Country dimension that participates in the Billing process in both Primary and Referer roles.Since a Billing process event can be related to more than one Country, the solution is going to involve modeling a many-to-many (M2M) relationship. Using a simple matrixification approach to address the M2M relationship to reduce the cardinality of the bridging dimension and the number of records in the bridging fact table, the separate primary and referer country references become concatenated into a single column in the fact table something like what’s shown below:

Matrixification

The dimensional model then would take that concatenated column and create the following structure,

  • DimCountryRoleMatrix – a bridge dimension with one row for each unique value of CountryRoleMatrix having a regular relationship with both Billing and CountryRoleMatrix measure groups,
  • FactCountryRoleMatrix – a  bridge fact table that contains two rows for each row in the DimCountryRoleMatrix table, one for country primary role and another for the country referer role,
  • DimCountry – one row for each country. The corresponding SSAS dimension would be related to the Billing measure group as a M2M relationship through the CountryRoleMatrix measure group, and
  • DimRole – contain only two rows, one for Primary and the other for Referer. Like Country, the corresponding SSAS dimension would also be related to the Billing measure group as a M2M relationship through the CountryRoleMatrix measure group.

DimensionalModel

The original question how to query for UK in the Primary role or France in the Referer role now in MDX is simply the following set:

{
( [Country].[UK], [Role].[Primary] ),
( [Country].[France], [Role].[Referer] )
}

 

Categories: SSAS, Data Warehousing

Enhancing the DateTool Dimension

October 8, 2012 1 comment

Technorati Tags: ,

I had this issue in the past and addressed it in a very inelegant manner involving a many-to-many relationship that I won’t reproduce here. For all you poor souls that inherited that approach, I sincerely apologize. I was young (not really) and inexperienced (also, not really) and knew when I implemented it, it was not the right way (definitely true). However, the users liked it, needed it, and once deployed, it was really difficult to retract.

The scenario that I’m addressing here has its roots in that same place. Say that I want to reproduce the Sales Performance Dashboard data visualization that Stephen Few presents in the last chapter of his book, Information Dashboard Design, within a SQL Server Reporting Services Tablix control. An example of this data visualization can be found here. The visualization includes a Sparkline, an Indicator, and a Bullet Graph for various different financial performance metrics. The difficulty in reproducing this visualization in SSRS is that all data used to generate these visualizations must be included in the same dataset. While the indicator and bullet graph are populated by Year-to-Date values, the Sparkline is sourced by current month values over the last twelve months. Therefore, two different date aggregation contexts, Year-to-Date and Last 12 Months, and two different date contexts, the selected month for the Bullet Graph and Indicator, and the component months for the Sparkline need to included within the dataset sourcing the visualization.

To produce a dataset that could be used to generation this data visualization, the DateTool dimension described by Marco Russo in this blog post was enhanced. The dimension will be referred to as the [Date Utility] dimension in the remainder of this blog post. These enhancements included adding new utility attributes to represent the component members involved in a date aggregation. To illustrate using the Adventure Works cube, if [Date].[Calendar].[May, 2008] and [Date Utility].[Aggregation].[Last 12 Months] are specified in the slicer, including the newly added [Date Utility].[Aggregate Month] attribute to the row axis would generate a set equivalent to [Date].[Calendar].[Jun, 2007] : [Date].[Calendar].[May, 2008]. This set represents the time periods that compose the ([Date].[Calendar].[May, 2008], [Date Utility].[Aggregation].[Last 12 Months]) calculated cell with the current cube context.

The first modification to Russo’s Date Utility dimension was to simply Cross Join the [DateTool].[Aggregation] and [DateTool].[Comparison] views with another view that included each possible [Date].[Calendar].[Month] member. (For this case, detail at the granularity of the Day level was not needed.) Even though the approach involves cross joining three views, each has a relatively small number of possibilities. The definition of the [DateTool].[CalendarMonths] and [DateTool].[DateTool] views are given below.

CREATE VIEW [DateTool].[CalendarMonths] AS
	SELECT	
		dt.MonthKey,
		dt.MonthDesc,
		dt.MonthStartDate,
		dt.QuarterKey,
		dt.QuarterDesc,
		dt.QuarterStartDate,
		dt.SemesterKey,
		dt.SemesterDesc,
		dt.SemesterStartDate,
		dt.Year
	FROM	DateTool.DimDate dt
	WHERE	dt.DateValue		= dt.MonthStartDate 
GO
CREATE VIEW [DateTool].[DateTool] AS
	SELECT	
		a.ID_Aggregation, 
		s.ID_Comparison,
		m.MonthKey, 
		(
			CAST( ID_Comparison AS VARCHAR ) + ' - ' 
			+ CAST( ID_Aggregation AS VARCHAR ) 
			+ ' - ' + CAST(m.MonthKey AS varchar)  
		) AS Description
	FROM	DateTool.DateAggregation a
	CROSS JOIN DateTool.DateComparison s
	CROSS JOIN DateTool.CalendarMonths m
GO

The second modification to Russo’s Date Utility dimension was to add an Aggregate Year –> Aggregate Quarter –> Aggregate Month natural hierarchy to the [Date Utility] dimension. A Business Information Model of the Date Utility dimension is shown below

image

The last modification is to define MDX script calculations to return cell values for the Aggregate Month, Aggregate Quarter, and Aggregate Year attributes. These additional calculations are placed between the script defining the Aggregation members and the script defining the Comparison members. This placement allows an aggregated comparison to also be decomposed into its constituent time periods.

The entire solution (at least with the Aggregate Month calculations defined) is available from my Skydrive account. To describe the approach, the definition of the subcube defined by the [Date Utility].[Aggregation].[Last 12 Months] member and [Date Utility].[Aggregate Month].[Aggregate Month] level will be described as shown in the code below. Only when a single member of the [Date].[By Calendar].[Month] dimension is included in the current context will a non-NULL value be returned. If that check passes, the next check is to determine if the current member of the [Date Utility].[Aggregate Month] attribute is within the last 12 months of [Date].[By Calendar].CurrentMember using an Intersect function call. If so, a tuple is returned that assigns the equivalent of LinkMember( [Date Utility].[Aggregate Month].CurrentMember, [Date].[Month] ); otherwise, a NULL value is returned.

SCOPE( 
    [Date Utility].[Aggregation].[Last 12 Months], 
    [Date Utility].[Aggregate Month].[Aggregate Month] 
);
    THIS = IIf(
        COUNT(EXISTING { [Date].[By Calendar].[Month].Members } ) = 1,
        IIf(
            Intersect(
                { [Date Utility].[Aggregate Month].CurrentMember },
                { 
                    LinkMember( 
                        [Date].[By Calendar].CurrentMember, 
                        [Date Utility].[Aggregate Month] 
                    ).Lag(11)
                    :
                    LinkMember( 
                        [Date].[By Calendar].CurrentMember, 
                        [Date Utility].[Aggregate Month] 
                    )
                }
            ).Count > 0,
            (
                LinkMember( 
                    [Date Utility].[Aggregate Month].CurrentMember, 
                    [Date].[Month] 
                ),
                [Date Utility].[Aggregation].DefaultMember,
                [Date Utility].[Aggregate Month].DefaultMember
            ),
            NULL
        ),
        NULL
    );
END SCOPE;

Lastly, to demonstrate its application, consider the following MDX statement which includes a single member of the [Date].[By Calendar] hierarchy and a single member of the [Date Utility].[Aggregation] attribute in the WHERE clause.

SELECT    
    {
        [Measures].[Internet Sales Amount]
    } ON COLUMNS,
    NON EMPTY
    {
        [Date Utility].[Aggregate Month].[Aggregate Month].Members 
    } ON ROWS
FROM    
    [Adventure Works]
WHERE
    (
        [Date].[By Calendar].[May, 2008],
        [Date Utility].[Aggregation].[Last 12 Months]
    )    

Including the [Date Utility].[Aggregate Month] attribute on the ROW axis returns the [Date Utility].[Aggregation] constituent members as shown below

Aggregate Month Internet Sales Amount
Jun, 2007 $554,799.23
Jul, 2007 $886,668.84
Aug, 2007 $847,413.51
Sep, 2007 $1,010,258.13
Oct, 2007 $1,080,449.58
Nov, 2007 $1,196,981.11
Dec, 2007 $1,731,787.77
Jan, 2008 $1,340,244.95
Feb, 2008 $1,462,479.83
Mar, 2008 $1,480,905.18
Apr, 2008 $1,608,750.53
May, 2008 $1,878,317.51

While this query doesn’t really illustrate why you would include an Aggregate Month enhancement, in a future post, use of the enhanced Date Utility dimension will be used to show how the Stephen Few data visualization including an Indicator, a Bullet Graph, and a Sparkline can be generated using Report Builder.

Categories: SSAS

The SSAS Financial Cube–Part 1a–Ragged Hierarchies Cont.

March 3, 2012 5 comments

In this post, I was supposed to discuss the ByAccount aggregation function. However, before changing focus, I thought I would write some more on Ragged Hierarchies.

In my last post, I discussed how ragged hierarchies can be handled in SSAS using two different approaches. One can model the dimension using a parent-child hierarchy or one can flatten out a parent-child hierarchy, model each potential level as a separate attribute in that dimension, and hide missing levels in an user-navigable hierarchy using the HideMemberIf property. The advantage of the first approach is that it is more dynamic and easier to implement; the advantage of the second is that it is more performant. But what I left out of the last post is a few additional advantages of the second approach. Rather than move on this week to discuss the ByAccount aggregation function or currency conversion, I want to discuss a situation where you have to address multiple ragged hierarchies in the same dimension. How do you handle the situation where you have an Account dimension and two different, maybe only slightly different but yet still different, rollups? One case that I encountered in the past was with a downstream oil and gas company where product transportation costs were included in COGS in one case but due to its unpredictable nature, were excluded from COGS in another. Essbase handled this situation beautifully as in our case, the Accounts dimension was modeled as a dense dimension. Using Essbase’s Shared Member functionality, a second rollup was created by simply defining that rollup in the Dimension Outline based on leaf members used to define the primary account rollup.

Implementing multiple ragged hierarchies in the same dimension is a bit more difficult to implement in SSAS than it was with Essbase. If you choose to model the ragged hierarchies as parent-child hierarchies, you’ll encounter problems as SSAS has a limitation that a dimension can have one and only one parent-child hierarchy. One can overcome that limitation by using the Multiple Parent/Child Hierarchies approach described by Marco Russo and Alberto Ferrari starting on page 70 of the often referenced Many-to-Many Revolution white paper. However, there’s a double performance penalty to be paid using that approach. Not only can you not build aggregations on the intermediate levels in the parent-child hierarchy, now the many-to-many relationship has to be resolved now when an account hierarchy is included in a query. If one sets the IsAggregatable property to False on an account hierarchy, performance can become unacceptable even with a relatively small cube.

The more performant approach will be to flatten out each hierarchy into a separate physical table where the key for each table is the leaf level accounts. A separate physical table is created for each hierarchy just to simplify maintenance as trying to create a single procedure to flatten out all hierarchies into a single physical table becomes a bit difficult to maintain. A view is created to join all tables representing the distinct hierarchies into a single representation. As each table is defined by a primary key on the leaf level account, a view uniting each of these tables would be easy to created. A single SSAS dimension is created from that view where a distinct user-navigable hierarchy is created for each physically flattened table.

Which solution works best for your application? Depends. If you need to respond dynamically to changing number of hierarchies or levels, an approach like that described in The Many-to-Many Revolution is probably going to be the best approach. If you need a solution that addresses aggressive performance requirements, flattening out each hierarchy and combining those hierarchies into a single dimension is going to be the better approach.

What’s been your experiences addressing multiple ragged hierarchies in a single dimension?

Categories: SSAS Tags:

The SSAS Financial Cube–Part 1–Ragged Hierarchies

February 26, 2012 6 comments

It’s been way too long since I’ve posted anything. As one of my new year’s resolutions was to blog more and it’s nearly March, I ‘m failing miserably at that goal. Not to make excuses but seems like at my day job all I do is write and when I get home, writing is the last thing I want to do. So after what seems like hours of commuting, at home I’ve been trying to make up for a lack of SharePoint knowledge, a product I have been trying to avoid for nearly a decade but have been unable to over the last few months. Maybe a post or two on that later just to embarrass myself.

So instead of embarrassing myself with little SharePoint knowledge that I’ve gained, I’m going to post on something that I know a bit about. I’m going to try to commit to writing a few posts around building a financial cube in SSAS. I’ll try not to completely duplicate previous blogs, particularly those of Garrett Edmondson.

On my first BI engagement over a decade ago, I was involved in a multidimensional database product selection. The potential choices were Hyperion Essbase, Microsoft OLAP Services 7.0 (pre-2000), Cognos whatever it was called at the time, and Oracle Express. The scope of the project was limited to the Profit&Loss accounts so we did not need to handle complex per account semi-additive measures, though I will discuss those later in another post. We selected Essbase and one of the principal reasons was that this product gracefully handled ragged hierarchies while the Microsoft offering did not. Well, since then, the Microsoft offering has matured quite a bit. Been some bumps along the way such as that annoying MDX Compatibility setting issue but seems like now the kinks have ironed themselves out. They just seem to work now. However, there’s still a way and probably a better way to implement.

What is a Ragged Hierarchy?

So first, what is a ragged hierarchy? MSDN actually has a great page defining a ragged hierarchy and how to work with one in SSAS. It may be the best page on the site! Succinctly, it’s a hierarchy with a varying number of levels dependent upon the navigation path taken. Usually, you’ll run into ragged hierarchies with financially based dimensions and particularly with an Accounts dimension. For Cost and Revenue related expenses, the navigation path may have many, many levels depending on the complexity of an business’ value chain while for Operational expenses, the navigation path may only contain just two or three levels.

There exists two ways to address the implementation of a ragged hierarchy; using a parent-child hierarchy or flattening out that parent-child relationship and taking advantage of the HideMemberIf property in a user-navigable hierarchy. Which approach you use depends on the requirements of your BI solution.

Parent-Child Hierarchy

The most common approach to addressing a ragged hierarchy is with a Parent-Child dimension. In fact, using a parent-child hierarchy is exactly how the Account dimension is defined in the AdventureWorks database as shown below.

Amount        
        USA Operations
Net Income Operating Profit Operating Expenses   $27,661,868.50
    Gross Margin Net Sales $64,626,468.00
      Total Cost of Sales $20,236,365.00
    Gross Margin Total   $44,390,103.00
  Operating Profit Total     $16,728,234.50
  Other Income and Expense Interest Income   $100,687.00
    Interest Expense   $154,750.00
    Gain/Loss on Sales of Asset   ($123,795.00)
    Other Income   $13,250.50
    Curr Xchg Gain/(Loss)   $214,625.00
  Other Income and Expense Total   $50,017.50
  Taxes     $4,168,749.00
Net Income Total     $12,609,503.00

So that the raggedness of the hierarchy is more evident, the PivotTable was displayed in tabular form.

What are the pros and cons of a parent-child hierarchy? Ella Maschiach does an excellent job describing the pros in her blog post. It’s also the approach that is the easiest to understand and the approach that is the easiest to implement. However, there’s a serious performance penalty that one pays for this simplicity. One of the primary reasons one uses SSAS in a BI solution is for aggregation management. Rather than constructing summary fact tables to address performance problems with the most commonly references dimensional attributes, aggregations in SSAS handle that for me. However, with a parent-child hierarchy, as mentioned in the Analysis Services Performance Guide, aggregations will only be created on the leaf level of the parent-child hierarchy and the All level only if the IsAggregatable property is not set to false. Therefore, with very large parent-child hierarchies that are used heavily in end user queries, there will be a tremendous performance penalty paid. If performance is at a premium, avoid parent-child hierarchies.

Naturalizing a Parent-Child Hierarchy

When your solution contains a parent-child hierarchy with many members, you’ll want to naturalize the dimension. This approach flattens out the parent-child hierarchy so that a separate column is explicitly created for each potential level in the hierarchy. You may want to still model your dimension using a recursive relationship because of its ease of maintenance. When creating a new member in the hierarchy, only the parent member would have to be specified. Once a dimension has been flattened, creating a new member requires that all ancestors of that member would also have to be specified on that dimension record.

From a recursive relationship, you’ve got a couple choices. You can flatten the dimension out first by using a CTE and then building your SSAS dimension from that structure. Itzik Ben-Gan described an approach in a SQL Server Mag article where a recursive relationship was flattened out so that a record was created for each node in the hierarchy. With that table, you can build a dimension where each level of the hierarchy is a separate attribute of the dimension. The dimension structure once flattened would look something like the following.

image

and the Attribute Relationships table for that dimension would define a natural hierarchy from Account Key –> Account Level 3 –> Account Level 2 –> Account Level 1.

image

Each level in this case is associated with a Row Number attribute that defines the natural order in which the hierarchy should be displayed.

When flattened in this manner, a ragged hierarchy is converted into a regular hierarchy where every navigation path has the same number of levels. For navigation paths where levels should be missing, the child name is repeated in that level as shown below.

Account Key 

Account Desc

Level 3 Key

Level 3 Desc

Level 2 Key 

Level 2 Desc

Level 1 Key 

Level 1 Desc

1 Net Income 1 Net Income 1 Net Income 1 Net Income
2 Operating Profit 2 Operating Profit 2 Operating Profit 1 Net Income
3 Operating Expenses 3 Operating Expenses 2 Operating Profit 1 Net Income
4 Gross Margin 3 Operating Expenses 2 Operating Profit 1 Net Income

To “hide” these levels, the HideMemberIf property of each level in the user-navigable hierarchy needs to be set to Parent Name as shown below.

clip_image003

Why flatten out a hierarchy when it’s much less intuitive and harder to maintain? Simple answer is while aggregations cannot be created on intermediate levels of a parent-child hierarchy, aggregations can be created on any level of a flattened hierarchy.

So say you have a parent-child hierarchy that’s performing less than optimally. Do you have to do all this to change it? Not exactly. You can use the Naturalize Parent-Child Dimension menu option in Solution Explorer that is installed with BIDSHelper. This added feature will do much of what’s been described in this section for you. Pretty cool.

Next Steps

Handling ragged hierarchies is just one of the requirements that’s required in constructing a financial. In a couple future posts, if I ever get around to it, I’ll discuss the ByAccount aggregation function that allows differing aggregation functions to be applied to different sections of an Account hierarchy, and a couple of approaches for addressing currency conversion.

Categories: SSAS Tags:

SSAS–Who/What’s Killing My Server?

February 2, 2011 3 comments

When diagnosing a problem with the database engine, one of the first tools I use is to execute a sp_who2 command to determine who’s running what. Wouldn’t it be that nice if there was an equivalent command for SSAS. Using SSAS 2008 DMV’s (Yaniv Mor blog post), one can use linked servers to create a nearly equivalent command. I created a simple DB Engine stored procedure, sp_SSAS_who, that accomplishes what I needed it to do. Below is the script:

CREATE PROCEDURE dbo.sp_SSAS_who AS BEGIN

        -- *******************************************************

        -- Variable declarations

        -- *******************************************************

        SELECT        ssasSessions.SESSION_SPID AS [SPID],

                ssasSessions.SESSION_USER_NAME AS [User ID],

                ssasSessions.SESSION_CURRENT_DATABASE AS [Database],

                ROUND(CAST(ssasCommands.COMMAND_CPU_TIME_MS AS int)/1000, 2) AS [Command Time(in sec)],

                ssasCommands.COMMAND_START_TIME AS [Start],

                ssasCommands.COMMAND_END_TIME AS [End],

                ssasCommands.COMMAND_TEXT AS [MDX Command]

        FROM        OPENQUERY(SSAS_LINKED_SERVER, ') AS ssasSessions

                        SELECT        *

                        FROM        $system.DISCOVER_Sessions

                '

        LEFT        JOIN OPENQUERY(SSAS_LINKED_SERVER, ') AS ssasCommands

                        SELECT        *

                        FROM        $system.DISCOVER_Commands

                '

                ON        ssasSessions.SESSION_SPID = ssasCommands.SESSION_SPID

SSAS_LINKED_SERVER is a linked server to my SSAS instance. You’d need to create the definition of the linked server on your box before this would be useful. Ideally, I should parameterize this so that the name of the linked server is a variable. One of the bonuses of this approach is that I can execute sp_who2 and sp_SSAS_who in the same window to see what’s going on with both services.

I apologize if someone else has published this approach elsewhere. I Googled and can up with zilch so thought I would post.

Categories: SSAS Tags:

Cube Wackiness – Two Different Internal Errors

August 21, 2010 2 comments

A couple of weeks ago, I posted a blog entry that demonstrated my ignorance on how cube space was defined in SSAS. Starting my BI career with Essbase, I still think in the block storage mode used by that platform. One of the strengths that Essbase had was that attributes could be created on stored members without requiring the cube to be reprocessed. I thought that an SSAS attribute which had its AttributeHierarchyEnabled property set to False would behave identically. That thinking is incorrect and even disabled attributes help define the cube space.

That feature worked great for me at the time as what once was a disabled attribute, through a significant shift in reporting requirements, now needed to be enabled. Re-enabling the attribute and deploying the changes, surprisingly to me, did not require that the dependent cubes be reprocessed. However, I had made a serious PEBKAC error. I changed the AttributeHierarchyEnabled property on the database dimension and also in the cube dimension of only one of my cubes. I had a linked measure group to another cube where the attribute hierarchy wasn’t re-enabled. SSAS let me go on so I thought everything was okay.

This week a very simple query on the linked measure group was run using this newly re-enabled hierarchy that was still disabled on the source cube.

SELECT  { 
                [Measures].[Measure from Linked Measure Group] 
        } ON COLUMNS,
        { 
                [Dimension].[Re-enabled Attribute].[Re-enabled Attribute].Members 
        } ON ROWS
FROM    [Cube with Linked Measure Group]
WHERE   ( 
                [Date].[By Calendar].[2009] 
        )
 

which returned the following:

 

Internal error: An unexpected error occurred (file ‘mdsubcube.cpp’, line 6260, function ‘MDSubcube::IsPartiallyCoveringCell’).

What’s also strange is when I changed the year from 2009 to 2010, a different error was returned.

Internal error: An unexpected error occurred (file ‘pfstore.cpp’, line 822, function ‘PFStore::CopyRecords’).

Apparently, when using linked measure groups, SSAS doesn’t like the situation when an attribute is enabled in one cube but not the other. When I enabled the attribute hierarchy in both cube dimensions, the errors were resolved.

Categories: SSAS

AttributeHierarchyEnabled and Cube Space Definition

August 9, 2010 Leave a comment

Sometimes I fall into the trap of assuming one product behaves like another. For instance, with Essbase, you could add an attribute hierarchy (really member properties) without requiring reprocessing of the cube. I thought the same with Analysis Services when an attribute was added to a cube dimension and the AttributeHierarchyEnabled property was set to false. BOL also seems to lead me to that conclusion. I would have thought that an attribute with the AttrbiuteHierarchyEnabled property set to false, since an attribute hierarchy isn’t created, would not have any impact on the cube space definition. However, if one adds a new disabled attribute hierarchy to a cube, it does require reprocessing of all measure groups related to that dimension.

To my delight this past week, I had to change the AttributeHierarchyEnabled property from False to True. I thought I would have to reprocess the entire cube. However, I was wrong. All I did was change the property value, deploy the changes, and a new attribute hierarchy was visible. Apparently, even attribute hierarchies with this property set to false still help to define the cube space.

Categories: SSAS