hello,
can I use fact table as one of the table in dimesion (snowflake schema)?
the situation is:
I've got invoice number in the level of the one sale line in fact table
and a dimesion table with types of sale documents. I want to have
hierarchy: DocGroup (comes from the dim table) -> DokKind (dim table) ->
DocType (dim table) -> InvoiceNo ( from fact table ).
can use the fact table in dimesion and can I use the dimension with cube
(Sale cube) based on the same fact table ?
am I far from the right way?
should I re-model my DW?

I use AS as OLAP server.

any suggestions?
thx

Edmo

hello,

It is possible to create a dimension based upon a fact
table.

I would prefer to build it from a dimension table. As the
time goes by... the processing of your dimension will take
more time (I am guessing that your fact table will grow at
a higher rate that your dimension table)

If that does not happen.... I would prefer a diferent
table... just in case... Imagine that in the future you
create another fact that also uses the same dimension.


Bye the way.... personaly... I prefer to create the
dimensions based on views and not on tables.

It is more flexible the managment of the dims (I guess it
may be a little bit slow). With views you can alter
descriptions and some of the structure of the dim wihout
touching AS2000.


Bye

Joao Campanico

True, you can build a dimension off the fact table, but it isn't a best
practice. Dimensions should be independent and stand-alone.
From your description so far, it sounds like you should be looking at either
drill-through or Actions to get your invoice data.
Check out those topics in Books-on-line.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Joao Campanico" <joao_campanico (AT) yahoo (DOT) com> wrote

That would work fine. However:
- How detailed are you getting with invoices (how many leaves are you
talking about)?
Might cause a problem.
- Sounds like you would have more flexibility having them be separate
dimensions.
- No other information on Invoices (to make it a dimension table etc...)?

Mark


"Edmo" <dmrozek99 (AT) poczta (DOT) onet.pl> wrote

Hi Mark,
thanks for the conversation

Mark Andrews wrote:
I'm not sure if I understand your question right but
I've got fact table granulated to each line from invoice.
It means each invoice line represents one product that is sold in the
transaction to one customer. There is many lines on the invoice, each
gives a record in fact table. So, many records in my fact table have the
same invoice number (which is a combination of numbers and characters
eg. 12345/XYZ/2002/1/1 ).
I would like to have a dimension with

e.g. to ask question: How many product X I sold on 'Sale documents'
(DocGroup) -> 'Invoices' (DocType) -> document number (InvoiceNo)
12345/XYZ/2002/1/1 ? (I missed one level of the hierarchy in the example)

or

want to know on what invoice I had so huge sale


I treated the fact table as one of my snowflake dim table
and made a cube with the same fact table as a fact table
but it works fine when I have 100 000 records in fact table.
When I have 2 mln or more that's a problem with processing dimension in
AS or processing cube with the dimension.
I created many partitions for the cube and it works but
I think it is trickie and I'm not sure if I should re-model my DW and
change point of view on the 'invoice problem'.

best regards
edmo