Mixing Aggregated data with non-aggregated data in a cube.

At this stage in the project I have a simple fact table which currently holds the low level data. I have designed and built a cube around this table and everything works fine with the current data.

The next stage of the project is to migrate some data into the cube from another business area. The problem is that this data is already grouped. I would like to display this data in the same cube so the users can at least get an overview of the different business areas. Is this practical and doable or are there caveats to doing this?

Does anyone have any suggestions as to what the best approach would be?

The classic approach to this problem is to create another measure group for the new set of data, and set the granularity of that measure group to match the granularity of data. I.e., if in the original measure group, the granularity by Time was Day, in the new one it might be set to Month. Some dimensions may be missing from the new measure group altogether etc.

Mosha.

|||

Interesting.... so is this what I need to do..(an educated guess):

Add the data to the same table|||

Since data is at different granularities - I suggest you keep it in different tables, not in the same table.

Once everything is done - you will be able to use your cube from Excel pivot tables or any other client tool. You will have multiple measures, some of them at one granularity and some at another.

Missing Value property of calculated member

Hi,

I defined several calculated measures in my cube. In Report Designer, i want to filter data depending a calculated measure.

Some caluclated measures are missing properties like value or ismissing. Anybody a hint, why these measures dont provide the properties, as for that filtering or hiding is not possible.

In AS every property is the sam for all calcs and the leaking calcs dont have difficult expressions (IIF(measure<0,measure/measure,0.0)

Thanks in advance

Please provide steps to repro this against the AdventureWorks sample cube.|||

Value property is working, even when it gets red underlined in visual studio.

So it works for me now.

Missing Value property of calculated member

Hi,

I defined several calculated measures in my cube. In Report Designer, i want to filter data depending a calculated measure.

Some caluclated measures are missing properties like value or ismissing. Anybody a hint, why these measures dont provide the properties, as for that filtering or hiding is not possible.

In AS every property is the sam for all calcs and the leaking calcs dont have difficult expressions (IIF(measure<0,measure/measure,0.0)

Thanks in advance

Please provide steps to repro this against the AdventureWorks sample cube.|||

Value property is working, even when it gets red underlined in visual studio.

So it works for me now.

Missing Properties?

Has anyone tried to open a cube into Business Intelligence Studio and the properties for the database vanished (project menu, properties)?

How do you get them back?

still haven't figured this one out|||

What property are you looking for?

In project mode there is only the database name property, but if you are working in online mode even this does not appear to be available. But the description, account settings and translations properties are all available under the "edit database" option in both modes.

Missing Distinct Count option in Measure's Aggregate Function property

I am trying to get a "distinct count" aggregate function specificed for a measure that is non-numeric. Is this possible? When I use the cube editor, the Distinct Count appears in the drop down list only when the measure is numeric or datetime. If the measure is varchar, the Distinct Count option is not there.
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.

Missing Distinct Count option in Measure's Aggregate Function property

I am trying to get a "distinct count" aggregate function specificed for a measure that is non-numeric. Is this possible? When I use the cube editor, the Distinct Count appears in the drop down list only when the measure is numeric or datetime. If the measure is varchar, the Distinct Count option is not there.
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.

Missing Distinct Count option in Measure's Aggregate Function property

I am trying to get a "distinct count" aggregate function specificed for a measure that is non-numeric. Is this possible? When I use the cube editor, the Distinct Count appears in the drop down list only when the measure is numeric or datetime. If the measure is varchar, the Distinct Count option is not there.
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.