Degenerate dimension with non-numeric data in SSAS

Dear,

I have the following problem/question:

I have a fact table that contains for example all orderline data, with next to that all skey's to my dimension tables (product, customer, date, ...). Next to the measures that are included in my fact table, I was planning to also include a degenerate dimension that contains my ordernumber. So far no problem creating the star schema in a relational database. But trying to set this up in SSAS, the problem comes up that my order number is not a real number but a string (eg 'ABC12345') and as far as I know, the fact table in SSAS cannot contain any string data (or am I wrong?).

Any suggestions?

Thank you in advance for your feedback!

Kind regards,

Jürgen

You are wrong :)

|||

OK, great! Can you also let me know how to add data formatted as string in my relational fact table to my SSAS cube (without creating an additional dimension)? I already tried a lot of things, but without any positive result...

Thx in advance,

Jürgen

|||I am not sure what you mean by "add data formatted as string". Are you talking about adding a varchar measure? I can't see any scenario where you would want to do that.|||

The notion of degenerate dimension in Analysis Services is modeled by using Fact dimension. Not sure why but legal guys didn't let us use "Degenerate" in the product . Must be the case of political correctness:)

You create a new dimension and you include all additional information you would like to see. You define a relationship between your dimension and the measure group as Fact. Then using Drillthrough functionatlity you should be able to provide your users access to this additional information.

Books online should have more information for you on the Fact dimensions and on the Drillthough.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thanks for the useful information, Edward!

Defining Non-Summable Measures in Cube

Hi all. I am new to SSAS 2005 and MDX in general. My team is building a data warehouse and thus far have the ETL (SSIS) part all done and am now moving on to the SSAS portion of the project. We have a cube built however, when browsing it via pivot tables, we noticed that the measures that are not additive, for example, "Average Log Length", show up as essentially sums over a given time period.

At first we thought that we needed to define the AggregateFunction for the specific measure to be AverageOfChildren. But that didn't work. The source data all has values of say 14.2, yet the average for a given day comes out to 2034.43! Clearly not an average.

This makes these non-additive measures completely useless in the cube.

Can anyone tell me what I am doing wrong? And how to correct it?

Any help would be much appreciated.

Thanks!

Jeff Ptak

Is the Type property of the time dimension specified as Time? The AggregateFunction semi-additive measure specification only applies to the Time dimension in your cube and will not work at all if no dimension is specified as a Time type.|||

This has come up before and the following is taken from this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=548837&SiteID=1

the AverageOfChildren is semi-additive, ie. it acts like a "sum" across all dimensions except time. For example, in Adventure Works if you add an AverageOfChildren measure on the OrderQuantity field of FactSalesSummary, its value matches that of [Measures].[Order Quantity] at the leaf Date level. But for July 2001 as a whole, [Measures].[Order Quantity] = 966, whereas the AverageOfChildren measure = 31 (which is 966 divided over 31 days of July).

Have you tried creating standard sum and count measures and then dividing the sum by the count? This should do the trick and you can hide the sum and count if they are not otherwise needed.

|||Yes. Set as Time.|||I have already read that post. I think I have it setup correctly, but the AverageOfChildren doesn't do what I want despite browsing by my Date dimension (which is configured as the "Time" type).|||

No, as that post explains, AverageOfChildren does not average across all dimensions, it sums across all other dimensions and only averages across the time dimension. It is not really well named, in fact in some of the wizards now it comes up as "Average over time".

Calculating a standard average of the values in the fact table can be done in a couple of easy steps.

1. Create a measure using a SUM aggregation

2. Create a second measure using a COUNT aggregation

3. Create a calculated measure that is (1) / (2)

|||

One last question. Are you deploying to Enterprise edition? Semi-additive measures are only supported in Enterprise.

http://www.microsoft.com/sql/editions/enterprise/comparison.mspx

|||

Yes. Deploying to Enterprise edition.

I'll try the sum/count thing. From what I have read, that seems to be common work-around to this problem.

Thanks!

|||It's not really a work-around, the averageOfChildren is meant to solve a different, more difficult class of calculation.|||

I figured this out. Simply set up a calculation for each non-summable measure that is:

Measure / Count

I made sure the AggregateFunction for the measure is set to SUM and I used the Count measure that the cube wizard inserted into my measure group (which has its AggregateFunction set to COUNT).

I would have thought that there was an easier way. I now have over 600 calculations in my my cube! And that's only for one process area of my facility.

Thanks for all your help.

Defining Non-Summable Measures in Cube

Hi all. I am new to SSAS 2005 and MDX in general. My team is building a data warehouse and thus far have the ETL (SSIS) part all done and am now moving on to the SSAS portion of the project. We have a cube built however, when browsing it via pivot tables, we noticed that the measures that are not additive, for example, "Average Log Length", show up as essentially sums over a given time period.

At first we thought that we needed to define the AggregateFunction for the specific measure to be AverageOfChildren. But that didn't work. The source data all has values of say 14.2, yet the average for a given day comes out to 2034.43! Clearly not an average.

This makes these non-additive measures completely useless in the cube.

Can anyone tell me what I am doing wrong? And how to correct it?

Any help would be much appreciated.

Thanks!

Jeff Ptak

Is the Type property of the time dimension specified as Time? The AggregateFunction semi-additive measure specification only applies to the Time dimension in your cube and will not work at all if no dimension is specified as a Time type.|||

This has come up before and the following is taken from this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=548837&SiteID=1

the AverageOfChildren is semi-additive, ie. it acts like a "sum" across all dimensions except time. For example, in Adventure Works if you add an AverageOfChildren measure on the OrderQuantity field of FactSalesSummary, its value matches that of [Measures].[Order Quantity] at the leaf Date level. But for July 2001 as a whole, [Measures].[Order Quantity] = 966, whereas the AverageOfChildren measure = 31 (which is 966 divided over 31 days of July).

Have you tried creating standard sum and count measures and then dividing the sum by the count? This should do the trick and you can hide the sum and count if they are not otherwise needed.

|||Yes. Set as Time.|||I have already read that post. I think I have it setup correctly, but the AverageOfChildren doesn't do what I want despite browsing by my Date dimension (which is configured as the "Time" type).|||

No, as that post explains, AverageOfChildren does not average across all dimensions, it sums across all other dimensions and only averages across the time dimension. It is not really well named, in fact in some of the wizards now it comes up as "Average over time".

Calculating a standard average of the values in the fact table can be done in a couple of easy steps.

1. Create a measure using a SUM aggregation

2. Create a second measure using a COUNT aggregation

3. Create a calculated measure that is (1) / (2)

|||

One last question. Are you deploying to Enterprise edition? Semi-additive measures are only supported in Enterprise.

http://www.microsoft.com/sql/editions/enterprise/comparison.mspx

|||

Yes. Deploying to Enterprise edition.

I'll try the sum/count thing. From what I have read, that seems to be common work-around to this problem.

Thanks!

|||It's not really a work-around, the averageOfChildren is meant to solve a different, more difficult class of calculation.|||

I figured this out. Simply set up a calculation for each non-summable measure that is:

Measure / Count

I made sure the AggregateFunction for the measure is set to SUM and I used the Count measure that the cube wizard inserted into my measure group (which has its AggregateFunction set to COUNT).

I would have thought that there was an easier way. I now have over 600 calculations in my my cube! And that's only for one process area of my facility.

Thanks for all your help.

Defining an Action in SSAS

Hi,

I have the need to build an Action on a measure that belongs to a cube that behaves like a DrillThrough Action that returns details related to a measure.

The problem is that the details data should come from a table that is not inside the cube but it is in an SQLServer DB.

So, it is possible to define an Action that could retrieve data from an external DB?

If so, how I'll write the action in BIDS (Target Type, Action Type, Action expression, etc.)?.

Thank you.

You would need to create a rowset action. I don't have sample code, but hopefully someone else will be able to provide you with that information.

|||

Thank you for the answer,

but you said to create a rowset action, can I use a "statement" action instead?

I mean, is there any reason that let you suggest me to use a rowset action instead of a statement action?

Please let me understand better. Thank you!

Defining a boolean dimension attribute

What's the best way to go about defining a dimension attribute for a boolean data value in SSAS 2005? In our first cut, we used a bit column in the database, but that results in dimension members of 0 and -1, and we'd rather have "true" and "false" displayed in the OLAP browser. In there an easy way to have SSAS map the bit values to the boolean strings?

You can create a named query in DSV to map the values on the fly.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Edward,
Thanks for the response. I did think of that solution, but it has a problem for me. As an ISV, our product needs to be localizable, and embedding string translations in the DSV presents a localization problem.

When I do a query against a bit column using SQL Server Management Studio, it displays the contents as true/false, and in the DSV the column data type is displayed as "boolean". So it seems strange to me that SSAS can't do that string conversion automatically. Perhaps in a future release - I'll report an enhancement request for it.

I ended up solving the problem by snowflaking in a "BooleanValues" table which contains members (and corresponding names) for true and false.

|||For posterity, this looks like it's an actual bug in SSAS when using the managed SqlClient provider. When you switch the data source to use the SQL Native client, bit column values are translated to true/false automatically. I've reported it on Product Feedback.