Dear Freinds,
I have hierarchy data in all dimensions and fact tables. The same is attached for your reference.
We have some dimension tables like
1. Year
2. Period
3. Entity
4. Custom1 ( Management )
5. Custom2 ( Product )
and fact tables which stores the fact data corresponding to
dimension ids.
Each dimension table stores the data in hierarchical form which starts from root node then goes to child node sequentially till leaf node occurs. In each record, we have ID and PARENTID of the records to identify the Parent record.
Similarly, In fact table, the data also gets stored only for the leaf node.
We need to roll up the fact data in such a manner that we should get a hierarchy data in tabular format which can be utilized in QlikView.
For Example,
In Period Dimension,
Year
Half 1 Half 2
Qtr 1 Qtr 2 Qtr 3 Qtr 4
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
ID | LABEL | PARENTID | PARENTLABEL | DESCRIPTION | ISSHARED | ISLEAF |
33,554,432 | [Year] | -1 | ( Blank Filled) | ( Blank Filled) | 0 | 0 |
50,331,648 | Half1 | 33,554,432 | [Year] | 1st Half | 0 | 0 |
67,108,864 | Qtr1 | 50,331,648 | Half1 | Quarter 1 | 0 | 0 |
83,886,080 | Jan | 67,108,864 | Qtr1 | January | 0 | 1 |
83,886,081 | Feb | 67,108,864 | Qtr1 | February | 0 | 1 |
83,886,082 | Mar | 67,108,864 | Qtr1 | March | 0 | 1 |
67,108,865 | Qtr2 | 50,331,648 | Half1 | Quarter 2 | 0 | 0 |
83,886,083 | Apr | 67,108,865 | Qtr2 | April | 0 | 1 |
83,886,084 | May | 67,108,865 | Qtr2 | May | 0 | 1 |
83,886,085 | Jun | 67,108,865 | Qtr2 | June | 0 | 1 |
50,331,649 | Half2 | 33,554,432 | [Year] | 2nd Half | 0 | 0 |
67,108,866 | Qtr3 | 50,331,649 | Half2 | Quarter 3 | 0 | 0 |
83,886,086 | Jul | 67,108,866 | Qtr3 | July | 0 | 1 |
83,886,087 | Aug | 67,108,866 | Qtr3 | August | 0 | 1 |
83,886,088 | Sep | 67,108,866 | Qtr3 | September | 0 | 1 |
67,108,867 | Qtr4 | 50,331,649 | Half2 | Quarter 4 | 0 | 0 |
83,886,089 | Oct | 67,108,867 | Qtr4 | October | 0 | 1 |
83,886,090 | Nov | 67,108,867 | Qtr4 | November | 0 | 1 |
83,886,091 | Dec | 67,108,867 | Qtr4 | December | 0 | 1 |
I also have uploded above data in a Excel file due to formatting issue if any. Please down load from Link below
http://rcpt.yousendit.com/870981867/9405992121834fb787f1d4e0339ca1d7
Each node contains unique ID to its parent
1. Year
2. Half 1
3. Qtr 1
4. Jan
5. Feb
6. Mar
7. Qtr 2
8. Apr
9. May
10. Jun
11. Half 2
12. Qtr 3
13. Jul
14. Aug
15 Sep
16. Qtr 4
17. Oct
18. Nov
19. Dec
In Fact Tables, considering a Period dimension we will have PERIODID field with value. Similarly \ we might have other dimensions like Year, Entity, Product,Management.
It's Data is below:
PERIODID VALUE
------------ ------------
83886080.00 8000.00
83886081.00 16000.00
83886082.00 24000.00
83886083.00 32000.00
83886084.00 40000.00
83886085.00 48000.00
83886086.00 56000.00
83886087.00 64000.00
83886088.00 72000.00
83886089.00 80000.00
83886090.00 88000.00
83886091.00 96000.00
I need a solution on this most urgent basis so that output will be converted to RDBMS format instead of Hierarchical.
Thanks a lot in Advance
