Degenerate Dimension with Partitions

Backgroud: My cube has 12 Partitions, and I set 12 fact tables to point to them. There is a Degenerate Dimension, which I point to fact table 11. By the Way, the Degenerate Dimension's Key is The fact Table 11's Table_ID, and the Table_ID is possible to be the same between diffrent fact tables 1 to 12.

Question 1: when I build the Degenerate Dimension, I can only choose one fact table to match, as you know. then if I ProcessFull the Degenerate Dimension, it will catch the data from other 11 fact tables? If the answer is YES, how about the same Table_ID?

Question 2: If I processFull the Question Degenerate Dimension, need I Process the Partitions 1 to 12, or only to Process the Partition 11, or not need to Process Partitions?

Question3: If I want to ProcessAdd the Degenerate Dimension,How could I do? I will decide by myself which fact table to chose new data?

Thanks, a lot.

Anyone know this?

I think this is a quite basic question if you want to use Degenerate Dimension, but nothing mentioned about this on MSDN.

Thanks.

|||

This previous thread in the Forum should address some of your questions:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=751457&SiteID=1

>>

Edward Melomed Posts 771	Answer Re: Fact Dimension Relationships on Partitioned Cube Was this post helpful ? Edit Post | Delete Post(s) | Split Posts | Lock Post
	If you decided to build your fact dimension based on the same table as your partitions are, you should create a view joining all paritition tables and base your fact dimension off that view. Otherwise the fact dimension is only going to show the invoice numbers that appear only in the first partition table.

|||

If you're going to ProcessFull or ProcessUpdate your degenerate dimension, then the instructions above are what you need. If you want to ProcessAdd your degenerate dimension (recommended) the following might help:

Some full working examples of ProcessAdd:
http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx

And some performance tests showing the performance of ProcessAdd on large dimensions:
http://www.artisconsulting.com/Blogs/tabid/94/EntryID/3/Default.aspx

Degenerate Dimension with Partitions

Backgroud: My cube has 12 Partitions, and I set 12 fact tables to point to them. There is a Degenerate Dimension, which I point to fact table 11. By the Way, the Degenerate Dimension's Key is The fact Table 11's Table_ID, and the Table_ID is possible to be the same between diffrent fact tables 1 to 12.

Question 1: when I build the Degenerate Dimension, I can only choose one fact table to match, as you know. then if I ProcessFull the Degenerate Dimension, it will catch the data from other 11 fact tables? If the answer is YES, how about the same Table_ID?

Question 2: If I processFull the Question Degenerate Dimension, need I Process the Partitions 1 to 12, or only to Process the Partition 11, or not need to Process Partitions?

Question3: If I want to ProcessAdd the Degenerate Dimension,How could I do? I will decide by myself which fact table to chose new data?

Thanks, a lot.

Anyone know this?

I think this is a quite basic question if you want to use Degenerate Dimension, but nothing mentioned about this on MSDN.

Thanks.

|||

This previous thread in the Forum should address some of your questions:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=751457&SiteID=1

>>

Edward Melomed Posts 771	Answer Re: Fact Dimension Relationships on Partitioned Cube Was this post helpful ? Edit Post | Delete Post(s) | Split Posts | Lock Post
	If you decided to build your fact dimension based on the same table as your partitions are, you should create a view joining all paritition tables and base your fact dimension off that view. Otherwise the fact dimension is only going to show the invoice numbers that appear only in the first partition table.

|||

If you're going to ProcessFull or ProcessUpdate your degenerate dimension, then the instructions above are what you need. If you want to ProcessAdd your degenerate dimension (recommended) the following might help:

Some full working examples of ProcessAdd:
http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx

And some performance tests showing the performance of ProcessAdd on large dimensions:
http://www.artisconsulting.com/Blogs/tabid/94/EntryID/3/Default.aspx

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 Hirarchies Accross Dimensions

I have a cube whose dimensions include Product and Product Group. The Product Group has a Many-To-Many relationship with the cube through a bridge table to the Product dimension. Whenever I want to see the sales for product by product group on the cube browser I associate these two dimensions manually (dragging and dropping to create a hierarchy). Is it possible to define this hierarchy in the Product or Product Group dimension? Thanks in advance

+ Product Group

++++ Product

No, it is not possible to create a hierarchy spawns several dimensions.

But on the other side, try to see is there way you might be able to model your Product dimension to include ProductGroup attribute in it.

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

|||

Thanks,

I know you should include these things in the product dimension, but the thing is that according to the business rules, these product groups could be anything because the users define these dynamically: regional areas, brands, etc. Moreover, a product could belong to many product groups and viceversa. This is why I had to create the group as a separate dimension.

Defining Analysis Services Named Calculation

Hi,

I am creating a new Named Caluclation for time dimension in dsv and added in the time dimension cube from the data source view in the time dimension structre.

While processing it is giving the error message that

"Memory error: The operation cannot be completed because the memory quota estimate exceeds the available system memory"

Can you please help me.

Thanks

Dinesh

What does the named calculation look like? And does it process if you take it out?

Cheers

Matt

|||

Show us the named calculation statment...

Works ok without the Named Calculation?!

Regards

|||

Hi

I have used the named calculation "ReportDate + ' ' + Session" in time dimension.

can you let me know how to add this namedcalculation to the cube after creating the named calcualtion in dsv.

Thanks

Dinesh

|||

Hi,

ReportDate and Session are both strings? If not you probably get problems, you will need to cast them as a string/varchar in order for your named calculation to work.

Adding to a dimension, just edit your dimension and drag it onto the attributes list. If it is a new measure, edit your cube and right click, add new measure, select the new named calculation.

Hope that helps

Matt

|||

You already created the NC in the DSV? Correct?

The cube processing generate errors only after your created the NC, correct?

Regards

|||

Hi,

ReportDate and Session are both strings. I added that into atribute list and processed.

It is giving the error message like

"Memory error: The operation cannot be completed because the memory quota estimate (1976MB) exceeds the available system memory (659MB). "

So, what to do for this type of error

Thanks

Dinesh

|||

Hi,

Looks like this is a known bug with SSAS, I haven't seen it before, but it looks like there is a hotfix for it.

http://support.microsoft.com/kb/914595

Hope that helps

Matt

|||

Hi,

Yes after creating the NC only this problem came. before i tested the cube there is no problem.

can you please let me know what is the problem.

Thanks

Dinesh

|||

Check the Matt link and fix the bug, if you will still need some help, tell us!

regards!

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 named set

I have built a cube and I want to add a named set. Following dimensions are important: suscriber and handset. The set I want to create should contain only those subscribers for which the handset is different from the handset from the month before.

I tried to use the filter function resulting in the following:

filter([Dim Subscriber].[Subscriber].[Subscriber].members,([Time].[Month],[Dim Handset].[Dim Handset].[Dim Handset])<>([Time].[Month].prevmember,[Dim Handset].[Dim Handset].[Dim Handset]))

But deploying the cube returns the error message that "<>" cannot be used with sets...

Can anybody help me? Thanks in advance...

Regards

Joos

Use MemberValue for handset

(See help at ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/f9b2af16-2b81-48e4-ae81-99f64e4bbc98.htm in Books online).

|||

But then I have to define a specific handset member. I want only those subscribers for which the handset in the current month is different from the month before. Maybe I do have to use the measure base, which simply counts all subscribers?

filter([Dim Subscriber].[Subscriber].[Subscriber].members,([Time].[Month],[Dim Handset].[Dim Handset].[Dim Handset],[Measures].[Base])<>([Time].[Month].prevmember,[Dim Handset].[Dim Handset].[Dim Handset],[Measures].[Base]))

But this returns the error: Error 1 The '<>' operator cannot be used with sets.

|||

You mentioned that [Dim Subscriber] and [Dim Handset] dimensions are important, but didn't describe the fact table data. Anyway, assuming that there are only fact records in a given month for valid combinations of Subscriber and Handset, it's still not clear how you define the current month. So, assuming that the current month is the last in the [Time].[Month] hierarchy:

>>

Extract(Filter(NonEmpty([Dim Subscriber].[Subscriber].[Subscriber].Members

* [Dim Handset].[Handset].[Handset].Members * Tail([Time].[Month].[Month].Members),

{[Measures].[Base]}),

IsEmpty(([Measures].[Base], [Time].[Month].PrevMember))),

[Dim Subscriber].[Subscriber])

>>

define default for date report parameter / analysis services

I have a report which will one day display some data from an analysis services cube. my first step is to create a drop down parameter enabling the user to choose the date. I'd like to display only dates that have data, and I'd like it to default to today.

So I've created a dataset that will be the datasource for the dropdown displaying the available non-empty dates, which works fine.

SELECT measures.turnover ON COLUMNS,

nonempty([TBL DIM DATE].[DATE_ONLY].[DATE_ONLY].ALLMEMBERS ) ON ROWS

FROM [Itdev1 Hk]

I've also set the report parameter up to be a queried paramter,and to use the above dataset as it source, with [DATE_ONLY] displayed. and [DATE_ONLY] as the value.

Now, how do I get it to default to the last valid member in the list?

I presume you are trying to have the latest date selected by default? If so, return your dataset in descending order (do an ORDER(<set>, DESC) on the rows), so that your latest date is at the top of the list.

|||this is helpful since now when I click the drop down the most likely values for me to use are at the top. but it has not caused any value to be selected by default.
|||You can use the same dataset in the default value query, which seems to collapse to the first row returned. I have no idea what the implications of doing this are, I happened on it by accident.|||

this is a helpful tip!

|||

i;m attempting to order by date in descending order. but it seems to sort in a random order when I do this ....

SELECT NON EMPTY { [Measures].[TURNOVER - WM INTERDAY] } ON COLUMNS, NON EMPTY { order([Time].[Date].[Date].ALLMEMBERS, [Time].[Date], desc ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Itdev1 Hk] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

and then in alphabetical order when I sort like this

SELECT NON EMPTY { [Measures].[TURNOVER - WM INTERDAY] } ON COLUMNS, NON EMPTY { order([Time].[Date].[Date].ALLMEMBERS, [Time].[Date].MemberValue, desc ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Itdev1 Hk] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

how do I get it to sort by real date?

default view for OLAP cube when first accessed

Hello,

I am using MS Analysis server for OLAP cubes and viewing these cubes using Cognos. What I want to do is to have defualt view defined for the particular cube whenever it is first accessed. I have been able to set the default month (i.e. current month). But in the default view I want data shown across multiple dimensions. E.g. on first view my client should be able to see data against dimensions Date and Product nested on y-axis and dimensions Destination and Time nested on x-axis.

Is it possible to achieve this using MDX query?

Any ideas will be greatly appreciated.

Thanks,

Akber

Hello!

I think that this can only be achieved if you have a client tool that supports writing MDX queries/selects. You cannot do it directly in AS2000 or SSAS2005, you will need a client. Cubes are not repositories for reports.

Since you are using Cognos you will have to rely of this supplier's tools for creating and saving reports.

HTH

Thomas Ivarsson