Fact Dimension

i have think not large fact table (approx 20 mil rows) of invoice line items.  there approx 4 mil uniquie invoiceid's listed in fact table.  seem have trouble getting invoiceid return dimension.  i've tried creating dimension based on fact table , using dimension.  i've tried pulling invoiceid out in separate table in dw, , creating dimension off of it. 

what happens is, i'll use filter (typically based on date , ship location) try , limit number of invoices in play.  then, when try add invoiceid dimension query usually fails due lack of memory. 

i should note, use cube browser testing client tools tableau, excel, , proclarity.  users won't know mdx, hope solution can done in as.

 

sam

hello! think way solve problem build separate invoice number dimension , create artifical levels in dimension.

the first level can first character/number in invoice number. on second level can use 2 first characters/positions , on.

with technique avoid problem of having ten thousand invoice number members(or more) being send down client.

it the  same cascading parameter in reporting services. forze user select small number of dimension members.

i proclarity professional there search capacity seldom used. see in dimension tool.

hth

thomas ivarsson

SQL Server  >  SQL Server Analysis Services