Connect two fact table

[MK]

I have two fact tables with different grain but the part of keys of both fact tables are the same.
For example 1 table have data grain that have document and accounting items and the other
fact table have document and hospital processes.

Now i want to get data from both tables that are conected via document. What is the principle of modeling? Is it better to have some
data in both tables or connect these tables to get all data that we needed.

Thanks in advance, KM

[rathjeevesh]

You should have Document as a dimension in you data model. Two fact tables 1 for accounting items and other for hospital processes. Get the summary for accounting items and hospital processes independently for document dimension and then merge the results. when you want to see data with more details use more common dimension like document and date dimension.

[MK]

Hi,

Thanks for replay and your advices. I have yust 1 more question. What if document details repeted in each fact table?
Dimension of Document looks to me quite big and then connect these tables together...will not cause performance problems?
Thanks in advance, KM

[rathjeevesh]

"document details repeated in each fact table" you mean to say currently document details is stored as textual fact? or you have document ID, type, description like columns. If details are descriptive then are you planning to join the two facts based on a varchar2 (2000) kind of a column?
My second opinion will be to verify the grain of you fact tables, check if they can be put as a single fact so that you do not have to join every time.

[MK]

Thanks for replay, in previous mail I do not gave all informations. Fact tables are connect via document number.
Then I have a lot of metrics in both tables, which I need it on the report. Because tables are connect one to many (a table of accounting deatails have many records in process fact table. Metrics from first table then must be repeted if there a more rows in the second table. How are you connect cases like this. We use olap tool - microstrategy.

Thanks in advance, KM

[Sponsored content]