AverageByChildren Function on a measure from a Fact and Dimension Table

Hi
I have a FactVendorPerformance table where in I have the CustomerID and the VendorID i.e. the Vendor who has serviced the Customer along with other details relating to the service quality of the vendor. I have also created KPIs in my cube for vendor service quality. I wanted a hierarchy - Customer-Vendor to filter the service quality KPIs since one customer could have been serviced my multiple vendors at different instances.
When I had the FactVendorPerformance table as only a FactTable, I was unable to create a Customer-Vendor hierarchy in it since it was not a dimension. So I made the FactVendorPerformance both a Dimension as well as a Fact table. However the measures have Sum as the default aggregate funtion. I changed this to AverageByChildren in the measure of FactVendorPerformance table. But this is not gettting reflected. Is there anything wrong in what I am doing? Is there any better way to implement the same?I want the Customer - Vendor hierarchy to filter the KPIs.

Thanks and Regards
Guruprasad Karnik

One customer could be serviced by multiple vendors - but, conversely, couldn't one vendor also service multiple customers? From a modelling perspective, customer and vendor would typically be separate dimensions - though they could be "stacked" for the purposes of reporting.

As far as the aggregate function, it's not clear how many fact records could exist for each combination of customer and vendor; but if you need to average a field value across all applicable fact records, you might try a calculated average wher you divide a "sum" aggregation on that value by a "count" measure for the same fact table/measure group.

|||Hi Deepak
Thanks for the reply:)! I did realise this Sum/Count workaround yesterday after going through some forums out here since AverageByChildren seems to average on only time and implemented the same. In my cube I do have Customer and Vendor as different dimensions. However the join or a relationship between the 2 exists only in the FactVendorPerformance table and all vendors need not have serviced all customers and conversly all customers need not have been serviced by all the vendors. It would be more meaningful to have a hierarchy in either ways (Customer-Vendor or Vendor-Customer) to filter the KPIs rather than having 2 different filters configured to 2 different dimensions. As you mentioned, conversely it can be a Vendor-Customer hierarchy as well. But the question is that since the relationship is existing only in the FactVendorPerformance table I had to make it a dimension table to get the hierarchy. I wanted to know as whether I am right in my approach. Is there any better way of designing or modeling the same?

Thanks and Regards
Guruprasad Karnik|||Not sure why you need to present 2 dimensions in a single hierarchy to users - what is the OLAP client tool? For example, with Reporting Services, if you wish to limit the selection list for vendors to those servicing a specific customer, you could use cascading parameters, where user selection of customer dynamically filters the selection list of vendors. In other words, can't you handle this navigation in the client, rather than in the cube?|||I am using MOSS 2007 KPI Lists to display the KPIs with the SSAS filter web part configured to the hierarchy and connected to the KPI list. If it was reporting services, then yes I could have had cascading parameters. But in MOSS 2007 I am not sure whether you can achieve this by interconnecting the filter web parts to have cascading parameters

AverageByChildren Function on a measure from a Fact and Dimension Table

Hi
I have a FactVendorPerformance table where in I have the CustomerID and the VendorID i.e. the Vendor who has serviced the Customer along with other details relating to the service quality of the vendor. I have also created KPIs in my cube for vendor service quality. I wanted a hierarchy - Customer-Vendor to filter the service quality KPIs since one customer could have been serviced my multiple vendors at different instances.
When I had the FactVendorPerformance table as only a FactTable, I was unable to create a Customer-Vendor hierarchy in it since it was not a dimension. So I made the FactVendorPerformance both a Dimension as well as a Fact table. However the measures have Sum as the default aggregate funtion. I changed this to AverageByChildren in the measure of FactVendorPerformance table. But this is not gettting reflected. Is there anything wrong in what I am doing? Is there any better way to implement the same?I want the Customer - Vendor hierarchy to filter the KPIs.

Thanks and Regards
Guruprasad Karnik

One customer could be serviced by multiple vendors - but, conversely, couldn't one vendor also service multiple customers? From a modelling perspective, customer and vendor would typically be separate dimensions - though they could be "stacked" for the purposes of reporting.

As far as the aggregate function, it's not clear how many fact records could exist for each combination of customer and vendor; but if you need to average a field value across all applicable fact records, you might try a calculated average wher you divide a "sum" aggregation on that value by a "count" measure for the same fact table/measure group.

|||Hi Deepak
Thanks for the reply:)! I did realise this Sum/Count workaround yesterday after going through some forums out here since AverageByChildren seems to average on only time and implemented the same. In my cube I do have Customer and Vendor as different dimensions. However the join or a relationship between the 2 exists only in the FactVendorPerformance table and all vendors need not have serviced all customers and conversly all customers need not have been serviced by all the vendors. It would be more meaningful to have a hierarchy in either ways (Customer-Vendor or Vendor-Customer) to filter the KPIs rather than having 2 different filters configured to 2 different dimensions. As you mentioned, conversely it can be a Vendor-Customer hierarchy as well. But the question is that since the relationship is existing only in the FactVendorPerformance table I had to make it a dimension table to get the hierarchy. I wanted to know as whether I am right in my approach. Is there any better way of designing or modeling the same?

Thanks and Regards
Guruprasad Karnik
|||Not sure why you need to present 2 dimensions in a single hierarchy to users - what is the OLAP client tool? For example, with Reporting Services, if you wish to limit the selection list for vendors to those servicing a specific customer, you could use cascading parameters, where user selection of customer dynamically filters the selection list of vendors. In other words, can't you handle this navigation in the client, rather than in the cube?|||I am using MOSS 2007 KPI Lists to display the KPIs with the SSAS filter web part configured to the hierarchy and connected to the KPI list. If it was reporting services, then yes I could have had cascading parameters. But in MOSS 2007 I am not sure whether you can achieve this by interconnecting the filter web parts to have cascading parameters

Average Over Time

Hi all,

I'm new to MDX and trying to accomplish the following.

I have a measure named [Bezetting] which comes from the following fact table:

OC_Date

OC_Category

OC_Number

I have 2 dimensions: Categories and Time.

Now I want to have a average over time measure like the one which is included in Enterprise edition (we only have the standard edition). I read you can do that with a calculated member.

I tried like this:

CALCULATE;

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].CurrentMember,[Time].Levels.Count-[Time].CurrentMember.Level.Ordinal,LEAVES), Measures.[Bezetting])

But that give me VALUE! instead of the averages in the browser.

Can anyone help me out?

TIA!!!

Stijn Verrept.

Try This:

Code Snippet

WITH MEMBER [Measure].[MyAVG1] AS

'AVG(

YTD([Time].[CurrentMember],

[Measure].[Bezetting]

)'

OR

Code Snippet

WITH MEMBER [Measure].[MyAVG2] AS

'AVG(

YTD(),

[Measures].[Bezetting]

)'

Helped?

Regards!|||helped?|||

No PedroCGD,

I tried your solution but the results I got were no averages.

I finally solved it like this: I first created a daycount measure:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date], [Time].[Year - Quarter - Month - Date].[Date]))

And then the average of bezetting got easy:

Code Snippet

IIF([Measures].[Bezetting] = 0,NULL,

[Measures].[Bezetting]/[Measures].[DayCount])

Thanks for your reply though!|||

I'm happy you get it!!

Mark your question as resolved to other people know!

Regards!!!

See you!

|||

The parameters to the descendants call do not look valid. The first call to .currentMember is missing a hierarchy reference and the second parameter to Level.Ordinal is redundant when using the leaves flag.

If you had a "Calendar" hierarchy it should look something like this:

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].Calendar.CurrentMember,,LEAVES), Measures.[Bezetting])

|||

Dear Darren,

Thanks for the reply, tried your solution but I got the same values as Pedro's. So maybe Pedro's solution was correct after all, I'll check out some more tomorrow, 3am here, gotta go to bed, not thinking clear anymore

Edit: ok checked some more. Solution of Pedro is giving me other values back. Your solution gives me the same, I also adjusted the DayCount script like this:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date],, leaves))

According to your example (I'm new at MDX) and it still works the same so I'll leave it like this, looks better! I use the daycount measure in other calculations as well.

|||

If you use the DayCount measure in other places you might get some performance benefits from the caching that SSAS does, so this probably a good solution.

Note that you may see a difference between

Measure / DayCount

and

Avg( <setOfDays>, Measure)

Because the Avg function will exclude days where there is not value for the measure, hence dividing the sum of the measure by a lower value.

Pedro's YTD() calculation should return the same value if you are selecting a year, but it should be different if you pick something like "Quarter 2" or a specific month as the YTD function will always return a set of members starting from the first member in a given year.

|||Great! Thanks for info, really usefull. Yes I was trying quarters and months as well.

Average Over Time

Hi all,

I'm new to MDX and trying to accomplish the following.

I have a measure named [Bezetting] which comes from the following fact table:

OC_Date

OC_Category

OC_Number

I have 2 dimensions: Categories and Time.

Now I want to have a average over time measure like the one which is included in Enterprise edition (we only have the standard edition). I read you can do that with a calculated member.

I tried like this:

CALCULATE;

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].CurrentMember,[Time].Levels.Count-[Time].CurrentMember.Level.Ordinal,LEAVES), Measures.[Bezetting])

But that give me VALUE! instead of the averages in the browser.

Can anyone help me out?

TIA!!!

Stijn Verrept.

Try This:

Code Snippet

WITH MEMBER [Measure].[MyAVG1] AS

'AVG(

YTD([Time].[CurrentMember],

[Measure].[Bezetting]

)'

OR

Code Snippet

WITH MEMBER [Measure].[MyAVG2] AS

'AVG(

YTD(),

[Measures].[Bezetting]

)'

Helped?

Regards!|||helped?|||

No PedroCGD,

I tried your solution but the results I got were no averages.

I finally solved it like this: I first created a daycount measure:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date], [Time].[Year - Quarter - Month - Date].[Date]))

And then the average of bezetting got easy:

Code Snippet

IIF([Measures].[Bezetting] = 0,NULL,

[Measures].[Bezetting]/[Measures].[DayCount])

Thanks for your reply though!|||

I'm happy you get it!!

Mark your question as resolved to other people know!

Regards!!!

See you!

|||

The parameters to the descendants call do not look valid. The first call to .currentMember is missing a hierarchy reference and the second parameter to Level.Ordinal is redundant when using the leaves flag.

If you had a "Calendar" hierarchy it should look something like this:

CREATE member CURRENTCUBE.[MEASURES].[GemBez] AS

AVG(Descendants([Time].Calendar.CurrentMember,,LEAVES), Measures.[Bezetting])

|||

Dear Darren,

Thanks for the reply, tried your solution but I got the same values as Pedro's. So maybe Pedro's solution was correct after all, I'll check out some more tomorrow, 3am here, gotta go to bed, not thinking clear anymore

Edit: ok checked some more. Solution of Pedro is giving me other values back. Your solution gives me the same, I also adjusted the DayCount script like this:

Code Snippet

Count(Descendants([Time].[Year - Quarter - Month - Date],, leaves))

According to your example (I'm new at MDX) and it still works the same so I'll leave it like this, looks better! I use the daycount measure in other calculations as well.

|||

If you use the DayCount measure in other places you might get some performance benefits from the caching that SSAS does, so this probably a good solution.

Note that you may see a difference between

Measure / DayCount

and

Avg( <setOfDays>, Measure)

Because the Avg function will exclude days where there is not value for the measure, hence dividing the sum of the measure by a lower value.

Pedro's YTD() calculation should return the same value if you are selecting a year, but it should be different if you pick something like "Quarter 2" or a specific month as the YTD function will always return a set of members starting from the first member in a given year.

|||Great! Thanks for info, really usefull. Yes I was trying quarters and months as well.

Autoslicing not working

I have around 36 partitions in the 2005 cube. Each partition is roughly 20M fact rows.

The partitions are differentiated by a date member dimension.

When I run a query which selects by a distinct date, SSAS tries to scan all partitions (for inclusions?). This introduces a huge performance impact. It takes many seconds to run a single query.

I have aggregations designed specifically for relevant dimensions. The Profiler shows that SSAS reads from aggregations. It does not read from facts.

The storage mode is MOLAP.

Do I need to use the partition slice property ? I am seeing on forums from time to time that this attribute is not used in MOLAP.

I just need to make sure that my queries are fast.

I don't see where it could hurt to set this property, though BOL indicates this is only used by SSAS for ROLAP partitions. I thought I had heard that with MOLOP and/or HOLAP, that SSAS maintained an internal set of information regarding the contents of a slice that would handle this function for you. Maybe someone else out there could chime in on this one.

Regarding the query you specify above, are you specifying a date member from the specific cube dimension used to partition your cube? You probably are but it wasn't explicitly stated. Date dimensions are often used in a role-playing capacity which introduces some confusion.

Thanks,
Bryan

|||

can anyone clarify if the Slice property on Partition is used in MOLAP or not?

I checked info....xml files for my slicing dimension min/max values and they seem correct, however with explicit queries, SSAS still reads a bunch of partitions...