Any thoughts?
Hi,
Did you get any reply and/or solution to this problem.
I have similar issue. Would appreciate if you can share.
Thanks !
|||This should work if you select the measure in the cube designer, and then go to the property sheet (F4). If you set the DataType of the binding Source to WChar for example, you should then set the DataType of the Measure to UnsignedInt. Once you do that, DistinctCount is a valid AggregateFunction.|||I am relatively new to Cube Editor, and trying to follow your suggestion....
1) I selected the Measure (CustomerID), then can see the Properties sheet at bottom (Basic and Advanced) -> then I chenged the data type VarWChar/Unassigned Integer, but when I go back to Aggregate Funcation I do not see the distinct count option.
2) Where do I change the "DataType of the binding Source" for this measure ?
BTW, I am using SQL server/Analysis Services version 2000.
Thanks for help !
|||Sorry, I was referring to SQL BI Studio in SQL 2005 in my reply above. For AS2000, there are some caveats described in the Analysis Services 2000 Books Online Help topic "Using Aggregate Functions" which may provide the answer (excerpts below):
Regular cubes are limited in their use of distinct count measures. Only one distinct count measure is allowed in a regular cube, and it is allowed only if the regular cube does not include a dimension with custom rollup operators or custom rollup formulas. Virtual cubes, however, do not share these limitations. A virtual cube can employ multiple distinct count measures and can also use custom rollup operators and custom rollup formulas in conjunction with distinct count measures.
Because distinct count measures are nonadditive, the presence of a distinct count measure significantly restricts the ability of Microsoft? SQL Server? 2000 Analysis Services to preaggregate the cube. For this reason, it is recommended that each distinct count be placed in its own cube with no other measures. These cubes with distinct count measures can then be joined together with other cubes in a virtual cube that efficiently manages all of the measures.
Note If a cube uses a distinct count measure, the entire cube is treated as nonadditive. Nonadditive cubes do not support dynamically created members, therefore MDX functions, such as VisualTotals, which dynamically create members, will return an error if used on a nonadditive cube. This also affects other features, such as enabling visual totals in dimension security, which involve dynamically created members.
|||Hi Scott Oveson,
I think you are the man to answer my question:
There are 2 tables in my database: OrderHeader,OrderDetail. And the OrderDetail table has different lines for different product. (standard sales order scenario)
If the OrderDetail table is the fact table, I want to design a measure to know many orders we have? I cannot simply use count(orderno) as the measure because the field,orderno, is duplicate in the OrderDetail table, and I cannot use "count distinct" for this measure because I have lots of other measures(do I have to use virtual cube?).
Any suggestions if I want to redesign this cube's structure?(in AS2000)
Thank you so much!
|||Scott Oveson wrote:
Sorry, I was referring to SQL BI Studio in SQL 2005 in my reply above. For AS2000, there are some caveats described in the Analysis Services 2000 Books Online Help topic "Using Aggregate Functions" which may provide the answer (excerpts below):
Regular cubes are limited in their use of distinct count measures. Only one distinct count measure is allowed in a regular cube, and it is allowed only if the regular cube does not include a dimension with custom rollup operators or custom rollup formulas. Virtual cubes, however, do not share these limitations. A virtual cube can employ multiple distinct count measures and can also use custom rollup operators and custom rollup formulas in conjunction with distinct count measures.
Because distinct count measures are nonadditive, the presence of a distinct count measure significantly restricts the ability of Microsoft? SQL Server? 2000 Analysis Services to preaggregate the cube. For this reason, it is recommended that each distinct count be placed in its own cube with no other measures. These cubes with distinct count measures can then be joined together with other cubes in a virtual cube that efficiently manages all of the measures.
Note If a cube uses a distinct count measure, the entire cube is treated as nonadditive. Nonadditive cubes do not support dynamically created members, therefore MDX functions, such as VisualTotals, which dynamically create members, will return an error if used on a nonadditive cube. This also affects other features, such as enabling visual totals in dimension security, which involve dynamically created members.
Are you trying to count the orders for each customer or the details of the orders for each customer? In AS2000 you'd need to use the virtual cube (if you want to get counts from another fact table). In AS2005 you could use multiple measure groups to do this.
|||Scott, i′m sure you can help me...i need to create a cube with 3 distinct-count measures, so i created 3 local cubes with 1 measure each one and 1 virtual cube using this measures.
When i′m creating this virtual cube the wizard asks me to specify the available dimensions. All my 3 local cubes uses exactly the same dimensions ( 36 at all for each cube ) but the wizard allows me to add only the dimensions of one single cube because it can′t contain duplicated dimensions ( and it′s not my intention to display in my pivottable the same dimension repeated 3 times).
When i try to see the data available in my virtual cube, only the measure contained at the local cube that i specified during the wizard is available, the other two measures appears empty. Do you know what i need to do to solve this problem ?
Thanks in advance.
|||It sounds like the problem is due to having the private dimensions (local to the cube) instead of using a shared dimension. Recreate the dimension as a shared dimension and use that in the cubes instead. Then when you create the virtual cube you should be able to do what you're trying to do. You'll need to reprocess the dimension and the cubes.
Hope that helps.
|||Scott,
Thanks for helping me, i did what you told me and worked fine.
No comments:
Post a Comment