How do you create a calculated measure that returns the count of fact table
records that matches a specific dimension table hierarchy? Can an example
MDX syntax be provided?
Thanks!
by Sandy Or » Sun, 03 Nov 2002 00:51:14
Thanks!
by [MS] Hai-Wei » Wed, 06 Nov 2002 16:17:05
MDX command queries the cells inside the cube. It won't go to the source
fact table and dimension table unless you send a "drillthrough" command.
This posting is provided 'AS IS' with no warranties, and confers no rights.
Regards,
Haiwei Xu
by Sandy Or » Wed, 06 Nov 2002 19:52:19
I don't think I was clear enough in my question. I basically want to create
a calculated member that is a ratio:
1. A simple example is if I divide a simple measure by another. This is
simple.
2. What I am trying to achieve is the following calculated member:
Numerator = Counts of Measure X belonging to dimension Y member.
Denominator = Total Count of Measure X.
For example, take fruit as a fact table (orange, apple pear etc.) and fruit
types as a dimension containing type 'citrus'. How can I create a calculated
member that gives the ratio of citrus fruits to total fruits?
Calculated Member = count(citrus fruits)/count(all fruits)
Thanks
Sandy
Quote:> Hi Sandy,
> MDX command queries the cells inside the cube. It won't go to the source
> fact table and dimension table unless you send a "drillthrough" command.
> This posting is provided 'AS IS' with no warranties, and confers no
rights.
> Regards,
> Haiwei Xu
by George Spoffor » Thu, 07 Nov 2002 00:33:19
> I don't think I was clear enough in my question. I basically want to create
> a calculated member that is a ratio:
> 1. A simple example is if I divide a simple measure by another. This is
> simple.
> 2. What I am trying to achieve is the following calculated member:
> Numerator = Counts of Measure X belonging to dimension Y member.
> Denominator = Total Count of Measure X.
> For example, take fruit as a fact table (orange, apple pear etc.) and fruit
> types as a dimension containing type 'citrus'. How can I create a calculated
> member that gives the ratio of citrus fruits to total fruits?
> Calculated Member = count(citrus fruits)/count(all fruits)
> Thanks
> Sandy
> > Hi Sandy,
> > MDX command queries the cells inside the cube. It won't go to the source
> > fact table and dimension table unless you send a "drillthrough" command.
> > This posting is provided 'AS IS' with no warranties, and confers no
> rights.
> > Regards,
> > Haiwei Xu
by Sandy Or » Thu, 07 Nov 2002 19:21:04
> > Thanks Haiwei,
> > I don't think I was clear enough in my question. I basically want to
create
> > a calculated member that is a ratio:
> > 1. A simple example is if I divide a simple measure by another. This is
> > simple.
> > 2. What I am trying to achieve is the following calculated member:
> > Numerator = Counts of Measure X belonging to dimension Y member.
> > Denominator = Total Count of Measure X.
> > For example, take fruit as a fact table (orange, apple pear etc.) and
fruit
> > types as a dimension containing type 'citrus'. How can I create a
calculated
> > member that gives the ratio of citrus fruits to total fruits?
> > Calculated Member = count(citrus fruits)/count(all fruits)
> > Thanks
> > Sandy
> > > Hi Sandy,
> > > MDX command queries the cells inside the cube. It won't go to the
source
> > > fact table and dimension table unless you send a "drillthrough"
command.
> > > This posting is provided 'AS IS' with no warranties, and confers no
> > rights.
> > > Regards,
> > > Haiwei Xu
> --
> George Spofford
> Microsoft MVP
> Chief Architect / OLAP Solution Provider
> DSS Lab
> http://www.dsslab.com
> ISVs & IT organizations: Find out how DSS Lab can speed your development!
1. Major MDX problems with Calculated member (this is a good test for MDX)
2. UK Vacancy -Ingres X-Train to Oracle/Developer 2000 4/2/98
3. MDX - Member properties in a calculated member
5. counting members - Calculated members?
6. TPCC test in postgreSQL 7.3 beta3
7. OLAP 2000 - Calculated member with a calculated parent member
8. Question about CHAR return from function
9. Creating an MDX Calculated Member as an Average over Time Dimension
11. MDX calculated member execution place: server vs. client
12. Performance of MDX calculated members