Wednesday, July 25, 2007

Data Member, Part 2

By Bryan C. Smith

In my previous post, you took a look at Amy, an employee who supervises three other employees, Jae, Rachel, and Ranjit.  Each of these four employees has sales quotas associated with them.  Since Amy is the supervisor of Jae, Rachel, and Ranjit, her sales quota reflects her personal sales quota as well as those from Jae, Rachel, and Ranjit.  You used the DataMember function to separate the sales quota directly attributable to Amy from her total sales quota figure:

with
member [Measures].[Personal Quota] as

     ([Employee].[Employees].DataMember, [Measures].[Sales Amount Quota]),

     format="Currency"

select

     {[Measures].[Sales Amount Quota],[Measures].[Personal Quota]} on 0,

     DESCENDANTS([Employee].[Employees].[Amy E. Alberts],1,SELF_AND_BEFORE) on 1

from [Adventure Works];


 

Sales Amount Quota

Personal Quota

Amy E. Alberts

$24,202,000.00

$1,124,400.00

Jae B. Pak

$12,547,100.00

$12,547,100.00

Rachel B. Valdez

$3,269,800.00

$3,269,800.00

Ranjit R. Varkey Chudukatil

$7,260,700.00

$7,260,700.00


 

Jae, Rachel, and Ranjit are referred to as leaf members of the hierarchy while Amy is referred to as a non-leaf member. Leaf members have no children while non-leaf members do.  (At this point, you haven't actually proven Jae, Rachel, and Ranjit have no children, but you will in the next query.)

In a parent-child hierarchy it is common for both leaf and non-leaf members to have data in the fact table directly associated with them.  The hierarchy's MembersWithData property determines how SSAS presents this data to you. 

If the MembersWithData property is set to NonLeafMembersHidden, which is the default when designed in BIDS, you get the behavior we see above – the values directly attributed to a non-leaf member are simply presented as part of the total value for that member. 

You've seen you can get to the value associated with the non-leaf member with the DataMember function.  Still, SSAS has the ability to present this another way.

If you set the MembersWithData property to NonLeafMembersVisible, SSAS will present the value directly attributed to a non-leaf member as a child of that member.  (Changing the value will require a re-deploy but not a re-process.) The easiest way to explain this is to simply demonstrate it:

with
member [Measures].[Personal Quota] as

     ([Employee].[Employees].DataMember, [Measures].[Sales Amount Quota]),

     format="Currency"

member [Measures].[Member Level] as

     [Employee].[Employees].CurrentMember.Level.Name

member [Measures].[Number of Children] as

     [Employee].[Employees].CurrentMember.Children.Count

select

     {[Measures].[Sales Amount Quota],

     [Measures].[Personal Quota],

     [Measures].[Member Level],

     [Measures].[Number of Children]} on 0,

     DESCENDANTS([Employee].[Employees].[Amy E. Alberts],1,SELF_AND_BEFORE) on 1

from [Adventure Works];


 

Sales Amount Quota

Personal Quota

Member Level

Number of Children

Amy E. Alberts

$24,202,000.00

$1,124,400.00

Employee Level 04

4

Amy E. Alberts

$1,124,400.00

$1,124,400.00

Employee Level 05

0

Jae B. Pak

$12,547,100.00

$12,547,100.00

Employee Level 05

0

Rachel B. Valdez

$3,269,800.00

$3,269,800.00

Employee Level 05

0

Ranjit R. Varkey Chudukatil

$7,260,700.00

$7,260,700.00

Employee Level 05

0


 

Take a close look at the results.  You've calculated the level name along with the number of children for each member.  The number of children identifies non-leaf and leaf members.  The member level helps you identify how these members are represented in the hierarchy.

With the MembersWithData property set to NonLeafMembersVisible, a leaf member representation of Amy is added to our results as a child of the Amy non-leaf member.  The non-leaf Amy member is just as before and the leaf Amy member simply holds the quota value directly attributable to Amy.

Understanding the difference between these two representations of Amy without the benefit of a walk-through such as this is a little confusing.  The MembersWithDataCaption property exists to address this problem.

The property accepts a string that serves as a template for the name of the SSAS-generated non-leaf member. An asterisk (*) in the string serves as a placeholder for the original member name.  A common string used for MembersWithDataCaption property is * (data).  Here are the results from the query above with the MembersWithDataCaption property set to this value:

 

Sales Amount Quota

Personal Quota

Member Level

Number of Children

Amy E. Alberts

$24,202,000.00

$1,124,400.00

Employee Level 04

4

Amy E. Alberts (data)

$1,124,400.00

$1,124,400.00

Employee Level 05

0

Jae B. Pak

$12,547,100.00

$12,547,100.00

Employee Level 05

0

Rachel B. Valdez

$3,269,800.00

$3,269,800.00

Employee Level 05

0

Ranjit R. Varkey Chudukatil

$7,260,700.00

$7,260,700.00

Employee Level 05

0


 

If you remember back to my previous blog entry, I started out with a simple breakdown of these sales quotas.  Using the MembersWithData and MembersWithDataCaption properties, we've come full circle having SSAS provide us the data in a similar structure:

  • Amy $24M Total

  • Amy (personal) $1M

  • Jae $13M

  • Rachel $3M
  • Ranjit $7M


 

So, what are the right settings for the MembersWithData and MembersWithDataCaption properties? It really depends on your requirements.  I recommend fully understanding how data is presented in a parent-child hierarchy and then sitting down with your end-user representatives to discuss the options. 

By their nature, parent-child hierarchies can be slightly difficult for users to wrap their heads around.  Walking users through a common example of parent-child hierarchy, such as employees in an organization, helps to explain the basic concepts. Using sample data sets, such as the AdventureWorks DW database, to illustrate these concepts can also assist with their understanding. 

It's also important to keep in mind these properties can be changed relatively quickly should you decide at a later date you would prefer your data presented another way.  Remember that changing the MembersWithData property affects the number of children associated with a non-leaf member which can have implications for calculations across levels in the hierarchy.  Thoroughly test any calculated members before and after changing this property, but also keep in mind calculated members can be re-deployed to your cube without re-processing.

Data Member, Part 1

By Bryan C. Smith

Take a moment to think about a typical organization.  You have an employee.  That employee has a supervisor who is also an employee who has a supervisor (who is also an employee) and so on and so on.   (This is a classic parent-child hierarchy.)

If these employees are in a sales organization, sales quota may have been assigned.  Those employees that are supervisors are responsible for the sales quotas of the employees below them but may also have sales quotas of their own.

Let's say you have an employee in a sales organization named Amy.  She supervises three other employees, Jae, Rachel, and Ranjit.  Amy is responsible for making sure these guys make their sales quotas of $13M, $3M, and $7M, respectively.  Amy also has a personal sales target of $1M.  We can breakdown Amy's sales quota like this:

  • Amy $24M Total
    • Amy (personal) $1M
    • Jae $13M
    • Rachel $3M
    • Ranjit $7M


 

The $24M figure represents the total sales for which Amy has responsibility while she is only directly responsible for $1M of that.  The remaining $23M represents the sales quotas she manages through her supervisees.

Let's now look at how SSAS 2005 handles all this. Open an MDX query window in SQL Server Management Studio, connect to the Adventure Works DW database, and execute the following query:

select

     [Measures].[Sales Amount Quota] on 0,

     DESCENDANTS([Employee].[Employees].[Amy E. Alberts], 1, SELF_AND_BEFORE) on 1

from [Adventure Works];

 

Sales Amount Quota

Amy E. Alberts

$24,202,000.00

Jae B. Pak

$12,547,100.00

Rachel B. Valdez

$3,269,800.00

Ranjit R. Varkey Chudukatil

$7,260,700.00


 

Through the DESCENDANTS
function, you asked SSAS to return the Sales Amount Quota for those employees one level below Amy E. Alberts and Amy herself. If you total the values for Jae, Rachel, and Ranjit, we come up with $23,077,600.00.  That's $1,124,400.00 short of the total. 

From these results, you can deduce the approximately $1M deficit represents Amy's personal quota.  But how do you get SSAS to explicitly present this?  You do it with the DataMember function:

with
member [Measures].[Personal Quota] as

     ([Employee].[Employees].DataMember, [Measures].[Sales Amount Quota]),

     format="Currency"

select

     {[Measures].[Sales Amount Quota],[Measures].[Personal Quota]} on 0,

     DESCENDANTS([Employee].[Employees].[Amy E. Alberts],1,SELF_AND_BEFORE) on 1

from [Adventure Works];

 

Sales Amount Quota

Personal Quota

Amy E. Alberts

$24,202,000.00

$1,124,400.00

Jae B. Pak

$12,547,100.00

$12,547,100.00

Rachel B. Valdez

$3,269,800.00

$3,269,800.00

Ranjit R. Varkey Chudukatil

$7,260,700.00

$7,260,700.00


 

Your results now show definitively that Amy owns a $1M personal sales quota which contributes to the $24M overall quota she is responsible for delivering.  You also see that Jae, Rachel, and Ranjit, Amy's supervisees, are solely responsible for their own quotas.  You cannot say Jae, Rachel, and Ranjit have no supervisees of their own (without submitting additional queries), but you can say none of these three guys is managing quota values from any supervisees they may have.

So, when you request a value for a member in a parent-child hierarchy, such as the Employees hierarchy of the Employee dimension, you need to be aware SSAS combines those values directly tied to that member with values associated with that member's children. You need to use the DataMember function to get at the value directly tied to a member.

It needs to be pointed out that you can tell SSAS to display a member's directly attributable data a little differently when that member also has children with values. In my next blog entry, we'll revisit Amy's sales team and take a look at those settings and how they impact these results.

IgnoreUnrelatedDimensions

By Bryan C. Smith

Imagine you have a highly simplified Adventure Works cube. In this cube, you have two measure groups, Reseller Sales and Internet Sales. Reseller Sales has relationships with the Geography and Date dimensions and has one measure, Reseller Sales Amount. Internet Sales has a relationship with just the Date dimension and a single measure, Internet Sales Amount. To simplify things even further, imagine the Geography dimension has a single attribute hierarchy, Country, and Date has three attribute hierarchies, Calendar Year, Calendar Quarter, and Date.

Every reference to data within this cube is based on a coordinate system. Each attribute hierarchy in the cube represents an axis in the coordinate. For our purposes, let's say the coordinates are in the format of ([Geography].[Country], [Date].[Calendar Year], [Date].[Calendar Quarter], [Date].[Date]). (For the sake of brevity, I've removed the attribute hierarchy from the Measures dimension.) Our MDX SELECT statements then assemble coordinates which SSAS resolve to return a cellset:


select


[Measures].[Reseller Sales Amount] on 0,


Geography.Country.Members
on 1


from [Adventure Works]


where ([Date].[Calendar Year].[CY 2003])

;

Reseller Sales Amount

Coordinates

All Geographies

$32,202,669.43

?

('All Geographies', 'CY 2003', 'All Times', 'All Times')

Australia

$847,430.96

?

('Australia', 'CY 2003', 'All Times', 'All Times')

Canada

$5,651,305.43

?

('Canada', 'CY 2003', 'All Times', 'All Times')

France

$2,373,804.04

?

('France', 'CY 2003', 'All Times', 'All Times')

Germany

$1,098,866.68

?

('Germany', 'CY 2003', 'All Times', 'All Times')

United Kingdom

$2,160,145.83

?

('United Kingdom', 'CY 2003', 'All Times', 'All Times')

United States

$20,071,116.48

?

('United States', 'CY 2003', 'All Times', 'All Times')


As was mentioned at the top of this entry, the Internet Sales Amount measure group does not have a relationship with the Geography dimension. Still, the cube's coordinate structure, ([Geography].[Country], [Date].[Calendar Year], [Date].[Calendar Quarter], [Date].[Date]), must be employed. With the measure group's IgnoreUnrelatedDimensions property set to True, which is the default setting for this property, the All member is substituted in place of any specified members from unrelated dimensions:

select

[Measures].[Internet Sales Amount] on 0,

Geography.Country.Members
on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

Internet Sales Amount

Coordinates

All Geographies

$9,791,060.30

?

('All Geographies', 'CY 2003', , 'All Times', 'All Times')

Australia

$9,791,060.30

?

('All Geographies', 'CY 2003', , 'All Times', 'All Times')

Canada

$9,791,060.30

?

('All Geographies', 'CY 2003', , 'All Times', 'All Times')

France

$9,791,060.30

?

('All Geographies', 'CY 2003', , 'All Times', 'All Times')

Germany

$9,791,060.30

?

('All Geographies', 'CY 2003', , 'All Times', 'All Times')

United Kingdom

$9,791,060.30

?

('All Geographies', 'CY 2003', , 'All Times', 'All Times')

United States

$9,791,060.30

?

('All Geographies', 'CY 2003', , 'All Times', 'All Times')


When the IgnoreUnrelatedDimensions property is set to False, SSAS does not perform any substitutions:

select

[Measures].[Internet Sales Amount] on 0,

Geography.Country.Members
on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

Internet Sales Amount

Coordinates

All Geographies

$9,791,060.30

?

('All Geographies', 'CY 2003', , 'All Times', 'All Times')

Australia

(null)

?

('Australia', 'CY 2003', , 'All Times', 'All Times')

Canada

(null)

?

('Canada', 'CY 2003', , 'All Times', 'All Times')

France

(null)

?

('France', 'CY 2003', , 'All Times', 'All Times')

Germany

(null)

?

('Germany', 'CY 2003', , 'All Times', 'All Times')

United Kingdom

(null)

?

('United Kingdom', 'CY 2003', , 'All Times', 'All Times')

United States

(null)

?

('United States', 'CY 2003', , 'All Times', 'All Times')


So, what about the situation where a measure group is only partially related to the attribute hierarchies within a dimension? Let's expand this simple cube to include a Sales Targets measure group. The Sales Targets measure group has a single measure, Sales Amount Quota, and has a relationship to just the Date dimension. Unlike the other measure groups which are related to the Date dimension at the Date attribute level, the Sales Targets measure group is related at the Calendar Quarter attribute level.

With IgnoreUnrelatedDimensions set to False on the Sales Targets measure group, we see the following behavior. Note, the number of rows has been limited with the HEAD function for display purposes only:

select

[Measures].[Sales Amount Quota] on 0,

HEAD([Date].[Date].Members,10) on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

Sales Amount Quota

All Periods

$38,782,000.00

January 1, 2003

(null)

January 2, 2003

(null)

January 3, 2003

(null)

January 4, 2003

(null)

January 5, 2003

(null)

January 6, 2003

(null)

January 7, 2003

(null)

January 8, 2003

(null)

January 9, 2003

(null)


Executing the same query with IngoreUnrelatedDimensions set to True, returns the following:

Sales Amount Quota

All Periods

$38,782,000.00

January 1, 2003

$38,782,000.00

January 2, 2003

$38,782,000.00

January 3, 2003

$38,782,000.00

January 4, 2003

$38,782,000.00

January 5, 2003

$38,782,000.00

January 6, 2003

$38,782,000.00

January 7, 2003

$38,782,000.00

January 8, 2003

$38,782,000.00

January 9, 2003

$38,782,000.00


Retrieving data at the Calendar Quarter level at which the relationship exists returns the following:

select

[Measures].[Sales Amount Quota] on 0,

HEAD([Date].[Calendar Quarter].Members,10) on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

Sales Amount Quota

All Periods

$38,782,000.00

Q1 CY 2003

$5,913,000.00

Q2 CY 2003

$8,039,000.00

Q3 CY 2003

$13,733,000.00

Q4 CY 2003

$11,097,000.00


What we can take from this is that when IgnoreUnrelatedDimensions is set to True, axis members associated with specific attribute hierarchies with which the measure group does not have a relationship are converted to the All member.

Monday, July 23, 2007

Greetings!

We have had a blog at SQL Junkies for a few years, but have not posted for a long time. With Word 2007's new post-to-blog feature, we decided to move to a new blog space. Blogger is pretty simple, so we're moving here.

We've accumulated a number of topics we want to blog about, and hopefully this will make it easier for us to post more actively. If we can figure out an easy way to do it, we may re-post some of our favorite entries from the old SQL Junkies blog.

Watch for more soon!

The Hitachi Consulting Microsoft BI Blog Team

Wednesday, July 4, 2007

The 'Currency' number format style is Evil [Reed]


I always used to use Currency formatting for currency values, but it's totally wrong in a database environment. Currency displays using the current locale's currency setting. But the values in your database have a pre-known, specific currency. If the values in your database are in Euros, then you don't want somebody from California to see them showing up as dollars. The origin of the Currency style was to simplify life for people using tools like Excel to input numbers. But when used with a database, they can cause rediculous restatements. Once I thought about it, Currency format is so rediculous that I can't believe I used to use it all the time. Hence the blog entry.

Even when doing currency conversions--you want to be in control of which currency format the values take. You NEVER want to be at the mercy of the current user's control panel whims.

Cheers. Happy 4th of July to all us Americans. [Reed]