BW on HANA Basics and Differences

[Former Member]

Hi All

I have a basic question regarding infocube structure before HANA and after HANA .

Befoe HANA The infocube strucutre is like the fact table is linked to SID tables via Dimension tables ,after HANA the infocube structure has changed and now the dimension tables has been removed and the fact tables are directly linked to SID tables .My doubts are as below

  1.Why the Dimension tables are basically used in BW (is it only to reduce the data redundancy or any other reaosn )?.

   2. Why Fact tables can't be linked to SID tables directly without Dimension tables in BW system without HANA.Why it is possible with HANA only.

Thanks

Santosh

[former_member184768 · Accepted solution]

Hi Varada,

Let me answer your second question first..

Fact Tables can be linked directly to SID tables even before HANA. The concept is LINE ITEM Dimension. It is possible even without having HANA.

The reason for having DIM tables is to reduce the size of FACT table. With all the SIDs added to the fact table for all the attributes, the fact table key is likely to grow very large resulting in high volume index for the fact table.

This index is likely to have negative performance on the Query.

One more thing, with SID, DIM and FACT tables structure (extended Star Schema), the no of joins required to produce the desired output also increases, but with manageable size of indexes on these tables make it possible to Query in better way.

Coming back to HANA, since everything is in memory, you can afford to have more no of keys for the fact table and reduce the joins to derive the output by eliminating the layer of DIM tables. Hence it is no longer required to have DIM tables.

Hope this helps,

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravindra

   Thanks for sharing your knowledge ,Yes I agree Line Item dimensions are there,but Line Item Dimensions  in BI are recommended when there is only one characterisitc per dimension if I am not wrong.

Suppose let me explain the below Inventory scenario,correct me if I am wrong.

Material  SID Table

----------------------------

ABC    111

DEF    222

XYZ    333

MatGroup  SID Table

--------------------------------

A        1

B        2

C        3

Plant SID Table

--------------------------

100    11

200    22

300    33

Target Inventory table

---------------------

ABC A 100 2000

ABC A 200 3000

ABC A 300 4000        

Dimension Table Entries

----------------------

DIMID  MatSID  MatgrpSID   PlantSID

1111   111     1           11    

2222   111     1           22   

333    111     1           33

Fact Table with DIM Table Scenario

-------------

DIMID       Quantity

1111        2000

2222        3000

3333        4000

Without DIMID The fact table would look like :-

----------------------------------------------

111 1 11  2000

111 1 22  3000

111 1 33  4000

in both DIM table and without DIM table scenario, the fact table have same no.of entries ,I could not understand how it would help in reducing the size of the fact table.Can you elaborate more .

Thanks

Santosh Varada

[former_member184768 · Accepted solution]

Hi Santosh,

Nice example. Kindly consider the following:

A Fact table contains 10 dimensions (max 16 for the Infocube) and each dimension containing 5 attributes. If these were to be added to the Cube directly it will result into 50 keys in the Fact table as compared to 10 dimension keys in the Fact table. Although the no of records are same, the number of keys (columns) in the fact table will vary a lot. This would have impact on the index generated on the Fact table. There is also a limitation on no of columns that can be added to the index which depends on the underlying database. Hence there is a limitation on maximum dimensions created in the fact table.

Hope this clarifies.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi

Thanks a lot,now it is cleared why the dimension tables are needed, although there is one more limitation on no.of primary keys and foreign keys also I believe . So there is no  limitation on no.of indexes for table in HANA,also  HANA by its nature its highly optimized because of in-memory , no need of  the dimension tables.

Correct me if I am wrong.The above mentioned reasons are the only once, are there any others behind this.

Thanks Once again for clarifying this.

Regards

Santosh Varada

[Former Member · Accepted solution]

Hi Ravindra,

I have liked your post.

As per my understanding, extended star schema has advantage of re-usable of MD, Multi-language, etc.

In HANA, we have attribute view, analytical view.  Analytical view uses attribute view and Facts.  Using attribute view in analytical view is helping to re-use MD data.  How is the Multi-language re-use possible?  If you have any such useful information, please share with us to understand this.

Thanks,

Srinivas

[former_member184768 · Accepted solution]

Hi Srinivas,

Ideally if a discussion thread is closed, then you should open another thread with the refernece of previous thread.

Anyway, The Multi-languade data or Multi-Language text is part of the attribute view. The multi-language text value depends upon the Language prefernece set for the attribute view. Since Attribute view is a re-usable object, the multi-language implementation will also be reused.

Is that what you are looking for or you need information on some other aspects of reusability.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi,

Thanks for update and will open new discussion next time onwards.

You have answered my question.

Thanks,

Srinivas

[virenp_devi · Accepted solution]

Hi Ravindra,

Even if it was SID to fact table direct relationship, Attribute values wouldn't be populated in the fact table. Attribute columns will still be referred from master data table by comparing SID value. Do you mean other way around here?

Ideally main reason I can think of introduction of DIM table is overcoming primary key limitation of the database. This way more than 16 keys(most of the databses)/chars can be included in the fact table indirectly (by foreign key relationship).

Regards,

Viren