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

Define relationship betwen Fact and Dimension (Fact have data but Dimension does not have data i

Hi

I have one problem in defining relationship between Fact and Dimension.

I have one Fact Table: FactTests

FactTests Fields: KeyDatetime, UnitId

and Two Dimension table : DimTests and DimASM

DimTests Fields: KeyDateTime, UnitId, OverallResult, TestCycle

DimASM Fields: KeyDateTime, UnitId, ASMResult

(DimASM row will exist only If TestCycle is 'A' in DimTests table)

KeyDatetime and UnitId is primary key in all Table.

I have define Regular Relationship between FactTests and DimTests, DimASM.

FactTests have data but DimASM have a data only when TestCycle is 'A'.

I am getting following error when I process the Cube.

Errors in the OLAP storage engine: The attribute key cannot be found: Table: FactTests, Column: KeyDateTime, Value: 2/1/2006 7:02:58 AM; Table: FactTests, Column: UnitId, Value: AA986495. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Dim Tests ASM of Dimension: Dim ASM from Database: SLC OLAP Database, Cube: OLAP Test Cube, Measure Group: Tests, Partition: Tests, Record: 1. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'Tests' partition of the 'Tests' measure group for the 'OLAP Test Cube' cube from the SLC OLAP Database database.

Regards,

Dinesh Patel

This has nothing to do with the type of relationships.

The problem is; during partition processing Analysis server saw the dimension keys coming from partition talbe (fact table) that it could match to the keys it read previously during dimension processing from the dimension table.

You need to make sure fact table has only the keys that are present in the dimension table. Make sure the columns you are joining between fact and dimension have the same data types.

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

|||

Our Test Table is splited in different tables. all common Data we are inserting in DimTests so FactTests and DimTests have no problem. some data we are inserting in DimAsm when we are performing ASM Test (When TestCycle = 'A') . If OBD Test is perform then we are inserting data in DimOBD (When TestCycle = 'O') that case KeyDateTime and UnitID not exist in DimASM but exist in DimTests and DimOBD.

so exect maching is not found between DimASM / DimOBD and FactTests.

what i will do in this case?

Define relationship betwen Fact and Dimension (Fact have data but Dimension does not have da

Hi

I have one problem in defining relationship between Fact and Dimension.

I have one Fact Table: FactTests

FactTests Fields: KeyDatetime, UnitId

and Two Dimension table : DimTests and DimASM

DimTests Fields: KeyDateTime, UnitId, OverallResult, TestCycle

DimASM Fields: KeyDateTime, UnitId, ASMResult

(DimASM row will exist only If TestCycle is 'A' in DimTests table)

KeyDatetime and UnitId is primary key in all Table.

I have define Regular Relationship between FactTests and DimTests, DimASM.

FactTests have data but DimASM have a data only when TestCycle is 'A'.

I am getting following error when I process the Cube.

Errors in the OLAP storage engine: The attribute key cannot be found: Table: FactTests, Column: KeyDateTime, Value: 2/1/2006 7:02:58 AM; Table: FactTests, Column: UnitId, Value: AA986495. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Dim Tests ASM of Dimension: Dim ASM from Database: SLC OLAP Database, Cube: OLAP Test Cube, Measure Group: Tests, Partition: Tests, Record: 1. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'Tests' partition of the 'Tests' measure group for the 'OLAP Test Cube' cube from the SLC OLAP Database database.

Regards,

Dinesh Patel

This has nothing to do with the type of relationships.

The problem is; during partition processing Analysis server saw the dimension keys coming from partition talbe (fact table) that it could match to the keys it read previously during dimension processing from the dimension table.

You need to make sure fact table has only the keys that are present in the dimension table. Make sure the columns you are joining between fact and dimension have the same data types.

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

|||

Our Test Table is splited in different tables. all common Data we are inserting in DimTests so FactTests and DimTests have no problem. some data we are inserting in DimAsm when we are performing ASM Test (When TestCycle = 'A') . If OBD Test is perform then we are inserting data in DimOBD (When TestCycle = 'O') that case KeyDateTime and UnitID not exist in DimASM but exist in DimTests and DimOBD.

so exect maching is not found between DimASM / DimOBD and FactTests.

what i will do in this case?