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

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@poczta.onet.pl> wrote in message
news:3F5D7D9B.6090109@poczta.onet.pl...
> 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
>

Hi Mark,
thanks for the conversation

Mark Andrews wrote:
> That would work fine. However:
> - How detailed are you getting with invoices (how many leaves are you
> talking about)?

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
> hierarchy: DocGroup (comes from the dim table) -> DokKind (dim table)
-> DocType (dim table) -> InvoiceNo ( from fact table ).

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


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

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