Querying Large dimension

View previous topic View next topic Go down

Querying Large dimension

Post  liranbn on Fri Mar 08, 2013 6:41 pm

Hi,

We have a system that tracks receipts in different stores.
most of the queries are aggregative ,but sometimes users also want to find receipt with strange behavior. when we try to query this dimension (that has millions of members) i mostly get timeouts or really bad performance.

I tries several of things;
1. make this degenerate dimension.
2. use Rolap.

I still can't find how i can make my users get to any insights when the dimension is receipts.
I know that low level is not best practice of cubes but i also can't just query the DB, I have many calculated measure that can't be converted to simple sql queries.

any suggestions?

Thanks,
Liran

liranbn

Posts : 2
Join date : 2013-03-08

View user profile

Back to top Go down

Re: Querying Large dimension

Post  ngalemmo on Fri Mar 08, 2013 7:23 pm

Why is there a receipt dimension? What 'behavior' are you looking for? Dimensions don't provide behavior, they provide context to behavior. Facts reflect behavior.

The point I am getting at is maybe there are fundamental flaws in the design that are causing the performance issues. In a typical model of receipts, receipt is an action (fact) surrounded by multiple dimensions (where received, from whom, when, what, etc...).
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Querying Large dimension

Post  liranbn on Sat Mar 09, 2013 9:28 pm

Hi,

I added this dimension from two reasons:
1. In our system we want to find different things about receipts. for example , shoe all receipt that their amount is bigger then X. the users want to be able to see those on the columns.
2. each receipt has bunch of attributes. And I want them to be able to slice the data by the receipts attributes.

These two requests lead me to the build the receipt dimension with all the attributes.

liranbn

Posts : 2
Join date : 2013-03-08

View user profile

Back to top Go down

Re: Querying Large dimension

Post  hang on Sat Mar 09, 2013 10:40 pm

I am with negalemmo on this. If you think of taking receipt as a business event, it would not be too hard to model it as a fact. The count and the amount are all additive measures, wherease all the attributes attached to a receipt are in the respective dimension tables to provide dimension context for receipt fact table where receipt number is just a degenerate dimension DD.

I guess it's a common mistake to model a business event as a dimension, be it a sale order, work order, application process, an insurance claim and accident. Somehow modelers are always trying to put facts in a container called dimension to be shared by other fact table, and they call it dimension conformance. It's less likely for a dimension to be modeled as a fact, as we all know to avoid textual attributes in the fact table.

Let me put it this way in this case. You can group all the relevant attributes in a few small dimensions connected to a receipt fact table so that you can also have these attributes as report columns by simply joining a bunch of tiny dimensions in a proper star schem, as opposed to a massive receipt dimension with its attributes repeated million times. If you need receipt reference in other fact tables or stars, you simply put receipt number as DD there to connect to all the relevant attributes through very light star joins, instead of connecting a massive and highly denormalised dimension called receipt dim.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Querying Large 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