Remove degenerate dimension?

View previous topic View next topic Go down

Remove degenerate dimension?

Post  ah on Wed May 18, 2011 5:16 am

We have a very large fact table (100 million records) of which some of the facts have the same dimensionality (all dimension IDs except the Fact ID).

This caused the problem of facts with identical dimensionality being aggregated into a single row in the drillthrough, so as a result we have had to create a degenerate dimension to seperate them out. This works well but unfortunately is extremely slow to process as the table is so big and we really need to speed this process up.

Is there any way that we can remove the degenerate dimension and have the drillthrough return the individual rows instead of the aggregated values? I have tried to create a fact measure on the fact ID and have it returned in the return columns, but this simply aggregates the ID even if it's aggregatefunction is set to none. Would it be possible to use a standard action to call some custom SQL to return the individual rows? What are the other options?

Any ideas would much appreciated.


ah

Posts : 1
Join date : 2011-05-18

View user profile

Back to top Go down

Re: Remove degenerate dimension?

Post  Dave Jermy on Wed May 18, 2011 9:14 am

It sounds like you haven't identified the grain of the fact table correctly. That is, identifying the combination of dimension keys that uniquely identifies a row on the fact. You shouldn't need to have a Fact ID - the primary key can be a composite of the dimension keys that give uniqueness.

Equally, you shouldn't have to 'create' a degenerate dimension - if there isn't one that already applies to the fact, deriving one simply to distinguish between otherwise identical facts doesn't really help in the long run.

Ask yourself this question: what is it that makes these facts distinct from each other? The answer should lead you to the dimension(s) that are missing from the fact table.

Dave Jermy

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

Back to top Go down

Re: Remove degenerate dimension?

Post  ngalemmo on Wed May 18, 2011 12:40 pm

I don't see a problem with dimensionality here. Fact tables do not need a primary key. If you have a sales fact, for example, and a customer orders the same product in the same day in the same channel, what does it matter that those dimensional keys are the same? It is what it is. Sure, you may have order number as a degenerate dimension, but it is not there to make a unique key.

The only purpose for a primary key is to update rows. If the load process does not update facts (and there are plenty of such processes that don't, which is a good thing), you don't need a primary key.

The issue here is the tool in use that is performing the drill-through. The simple answer here is it should not do an aggregate query, because that is not what you want it to do.

What is it trying to query? A cube or a relational database? If it is a cube, there is nothing you can do other than keep the extra dimension, cube (i.e. multi-dimensional) databases naturally work in aggregate. If the drill down source is relational, it is a matter of adjusting the SQL being used. Most tools have ways of providing SQL overrides. Also, some cube based tools allow you to redirect queries to a relational source (i.e. drill-through).

Since you used the term 'drill through' I am assuming you have a cube based application and the drill query is hitting a relational source. The term 'drill down' is commonly used when you move to greater detail within the same data souce (cube or relational) while 'drill through' refers to moving to greater detail against a different data source (usually relational). If that is the case, your tool should have some means to adjust or override the drill-through SQL.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Remove degenerate dimension?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum