Showing posts with label degenerate. Show all posts
Showing posts with label degenerate. Show all posts

Monday, March 19, 2012

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

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!

Degenerate dimension vs synthetic lookup

I'm trying to get a handle on a modeling question of sorts. Basically I have a fact table with ~ 10 million rows. This table has a column called lot_id for each of several components (lets say componentn_lot_id to be generic). My componentn_lot_id has ~ 1.5 million distinct values. Theoretically speaking is it better to treat this attribute as a degenerate dimension of the fact table or would it be better to synthesize a lookup by creating a named query like:

select component1_lot_id from fact_table group by 1

and using this as a lookup table for a regular dimension? I have about 12 dimensions like this, so the simplicity of the degenerate dimension method is nice, but seems to have a pretty high price to pay when processing the cube (though the lookup method may also be costly). Are there other issues to keep in mind here?

I'm seeking advice as to rules of thumb (or techniques) that should be used when faced with this decision. I am currently using AS2005.

Thanks,

Keehan

Are you using ROLAP or MOLAP?

For MOLAP storage, very probably there's no difference regarding processing time.

You might want to take some measurements anyway, if only to make sure scalability and sizing requirements are met...

Hope this helps

degenerate dimension and partitioning

I needed to provide drill down to some granular attributes, so I setup a degenerate dimension with several attributes like serial_number, etc. Doing so has somewhat understandably caused my processing time to increase from 2 minutes to 45 minutes for my sample set of data.

To handle this increased processing time, I'm attempting to use partitioning. Now, the trouble I'm running into is how do I process the degenerate dimension in step with the partitions. I am getting errors from the process that boil down to fact_key does not exist in the degenerate dimension so the fact can not be joined to the fact dimension table. How do I process a degenerate dimension in step with my partitions. Does that make sense?

Keehan

Try to see where most of the time of your processing goes. Try to process dimensions and partitions separately.

Is processing partitions becomes way slower? Or it is dimension processing that takes most of the time?

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

|||

The same question.

If we use 12 partitions, and set 12 fact table to them, how about the fact dimention? it will get new members from diffrent fact tables by itself correctly?

degenerate dimension and partitioning

I needed to provide drill down to some granular attributes, so I setup a degenerate dimension with several attributes like serial_number, etc. Doing so has somewhat understandably caused my processing time to increase from 2 minutes to 45 minutes for my sample set of data.

To handle this increased processing time, I'm attempting to use partitioning. Now, the trouble I'm running into is how do I process the degenerate dimension in step with the partitions. I am getting errors from the process that boil down to fact_key does not exist in the degenerate dimension so the fact can not be joined to the fact dimension table. How do I process a degenerate dimension in step with my partitions. Does that make sense?

Keehan

Try to see where most of the time of your processing goes. Try to process dimensions and partitions separately.

Is processing partitions becomes way slower? Or it is dimension processing that takes most of the time?

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

|||

The same question.

If we use 12 partitions, and set 12 fact table to them, how about the fact dimention? it will get new members from diffrent fact tables by itself correctly?

Degenerate Dimension

Experts:

Pardon me for this basic question. A degenerate dimension as I understand does not have a parent dimension (pls correct me if I am wrong) in the fact table. In that case is it repeated in the fact table?

For example if i have the following fact table

Productkey(FK)

Customerkey(FK)

Timekey(FK)

TicketNumber

All the first three (product,customer and Time) have dimensions while Ticketnumber has a orphan parent. In that case does its value get repeated in the fact table or is it usually a unique value?

Please explain.

Thanks

Ankith

Hello.

You are correct that ticketnumber do not have a parent and is a degenerate dimension.

If you sell the same ticket several times it can appear several times in the fact table. That is normally not the case. Think of a bus ticket, a cinema ticket or an airline ticket. For these later scenarios you will not see a repeted ticketnumber.

HTH

Thomas Ivarsson

|||

Hi Thomas:

Thank you very much for a lucid and beatiful explanation with examples. Appreciate it.

Best Regards

Ankith