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