Fact Dimension

I have what I think is probably not a large fact table (approx 20 mil rows) of invoice line items. There are approx 4 mil uniquie invoiceID's listed in the fact table. I seem to have trouble getting the invoiceID to return as a dimension. I've tried creating a dimension based on the fact table and using this as a dimension. I've also tried pulling the invoiceID out in to a separate table in the dw, and creating a dimension off of it.

What happens is, I'll use some filter (typically based on date and ship location) to try and limit the number of invoices in play. Then, when I try to add the InvoiceID dimension the query usually fails due to lack of memory.

I should note, I use the cube browser for testing as well as some client tools like Tableau, Excel, and Proclarity. My users won't know MDX, so I hope a solution can be done in AS.

Sam

Hello! I think the way to solve this problem is to build a separate invoice number dimension and to create artifical levels in this dimension.

The first level can be the first character/number in the invoice number. On the second level you can use the two first characters/positions and so on.

With this technique you will avoid the problem of having ten thousand invoice number members(or more) being send down to the client.

It is the same as a cascading parameter in Reporting Services. Forze the user to only select a small number of the dimension members.

I ProClarity Professional there is a good search capacity that is seldom used. You will see it in the dimension tool.

HTH

Thomas Ivarsson