Querying Large dimension

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

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...).

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.

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.