Want to understand SID concept...

[Former Member]

Hello experts,

why not using the value like e. g. customer number directly in the dimension table instead of a SID? So I also could save one table (SID table) and with this on JOIN by linking directly to the master data tables (text, attributes, hierarchies) using customer no. as key.

Am I wrong? Is it only because of this INT4 thing with the SIDs whitch maybe results in better performance?

What I maybe understand is compounding, it seems to be hard to make here a connection to the right master data table. But if I wouldn't have this case, why else could be this SID useful?

Best regards,

Peter

[Former Member]

Greetings,

As per my understanding, there are 2 purpose served by SIDs:

1. First we should see the SQL code which is triggered when you execute a query.
Here's what happens:

Whenever you trigger a query like give me all sales for Bangalore in the first quarter of current year. Now system cannot go to the fact table since it contains all numerics. SO it needs to know which numbers in fact table corresponds to Bangalore (Location) and First quarter (0CALQUARTER) and current year (0CALYEAR). Now alphabets are 26 and numbers are 10 (0-9) so the permutation for alphabets are much more than numbers so having numbers is nothing but creating an index for that column. So first the query reads SIDs, then goes to DIM IDs and gets DIM numbers and then searches those in F table. Now if you store alphabets then fields like 0Employee address will have so many chars and finding amongst that will be a colossal task!

2. Second use of SIDs are reusability. Master data tables are used in many cubes. But if you store values directly in dimension tables, then for each cube the dimension table values will be stored redundantly so loosing reusability feature !

Reg

Deb

[Former Member]

Hi Peter,

I guess I understand your question and I think this is a pure performance thing as it is much easier to go thru numeric values instead of character information, specially if you have long character fields for your characteristics.

Maybe [this|http://en.wikipedia.org/wiki/Star_schema] is of some help.

regards

Siggi

[Former Member]

Hello Siggi,

thank you for your answer and the linke you provided. I think star schema concept and using surrogate ID's for dimension tables is absolute clear for me and I see the advantage there.

How I said, faster DB access using INT4 values instead of characters is also a point I understand. On the other side SAP BW offers you to use a line item dimension which saves a join and result in a better performance.

But why not give the BW developers the chance to decide if you want use it? If your InfoObject is a numeric one, you are may be much faster, saving a join and having an even easier to handle value.

What I am interested in is, if SID technology is so deeply part of SAP BW, for example if you want to report on an DSO you also need SID's, what is the real advantage usind SID's every time for access of InfoCube or DSO data?

See, i read for example the following advantages of BW star schema on [this site|http://learnmysap.com/sap-bw/138-classic-and-sap-bw-star-schema-comparisons.html]:

-> Faster access to data than via long alpha-numeric keys. SAP BW use automatically generated INT4 keys for SID and Dimension ID

-> Can model in easy way: Historizing, multi-lingual, and shared dimensions. It is happen because of the excavation of master data from the dimension tables using the SID technique.

-> The query performance is improved here as aggregated key figures can be stored in their own fact tables.

OK, first point about INT4-keys we discussed.

I completly don't understand the second point and why therefore SID's are needed.

Third point depends on disadvantages of the classical star schema compared to bw aggregates, a point I understand

Often I read: The SID table provides the link between the master data and the dimension tables. (like here: /people/githen.ronney3/blog/2008/02/13/modeling-strategies)

But why shouldn't this be possible just with the key value?

Even SAP just describes his enhanced star schema without SID's, but technically SID's are used. Why not use it like SAP itself describes here:

http://help.sap.com/saphelp_nw70ehp1/helpdata/en/4c/89dc37c7f2d67ae10000009b38f889/content.htm

regards,

Peter

[Former Member]

Hi Peter,

well I guess this is more a philosophical thing. I was asking myself the same question a few years back (well I started BW with first customer shipment of 1.2A) and couldn't find an answer as well. The only thing I can imagine is the consistency of the complete data models having numeric keys all over and not a mixture of date fields, character fields, numeric fields.... when it comes to joining all the stuff together.

I will pass the thread to the mentor forum and will see if there will be some more feedback.

regards

Siggi

[Former Member]

Hello Siggi,

thank you for your answer. With colleagues I discussed in a similar way.

I hadn't known that a mentor forum exist, thank you for posting there!

Further answers or ideas from experts welcome!

regards,

Peter

[Vitaliy-R]

Hi Peter,

You touched already couple of threads, but going to you primarily question - why SIDs are enforced in BW, I believe we would need to look into historical and philosophical view points.

Historically BW OLAP engine can work only with SIDs (don't ask me why , that's why SIDs have to be generated even when reporting on DSO. Please remember that although joins are happening in SQL, there is as well lots of calculation happening in OLAP on ABAP structures.

Philosophically, it is a broader question of the level of flexibility delivered by DW packaged platform as BW: why SIDs are always enforced, why only 15 dimensions on InfoCubes, why no out-of-the-box db partitioning for DSO active and MD tables etc etc.

On the second topic of the link with advantages of BW star schema; those are advantages of BW extended star schema, not advantages of SIDs.

Cheers,

-Vitaliy

[Former Member]

Hello Vitaly,

thank you for your answer! I assume my question won't be answered finaly. Maybe on day I have the chance to speak with the SAP developer who first have this Idea to say "let's make this SID thing'.

Sorry, I'm just such a person who want to know it exactly...

You are right, my link is about extended star schema. But very often in such discussions I found as a explanation for the advantages that it is because of SID's like -> 'It is happen because of the excavation of master data from the dimension tables using the SID technique.'

Best regards,

Peter

[Former Member]

Hello Alexander, hello Vinay,

thank you for your input. The MDM document is very interesting. Haven't read it for a long time

But I think it doesn't contain what I want. This is just what SID's do and how they are part of the BW data model.

This link about Dimension also doesn't help me. Sorry. I think I searched the whole SAP Help BI part about SIDs.

To repeat my fist question: why not using the value like e. g. customer number directly in the dimension table instead of a SID?

The only advantages I see are as follows:

-> Using INT4-Values makes faster DB access, but to do this I need an additional join over maybe some large tables (like for example 0MATERIAL: /BIC/SMATERIAL->/BIC/PMATERIAL )

-> modelling compounds. If you have a compound key (for example 0MAT_PLANT with compounded key 0MATERIAL and 0PLANT) it could easier using a surrogate ID.

But despite of this points, why using SIDs everywhere, ever in enhanced BW star schema. Why not if SID's realy make sense like having compounds or large alphanumeric fields?

What I also read as an advantage was that master data is separated from transactional data. I understand this concept which is similar or equal to a snowflake schema. But why should I need SID's for this?

regards,

Peter

[Former Member]

In the link below pl chk path

Business Intelligence --> Data Warehousing --> Modeling --> InfoProviders --> InfoCubes --> Dimension

http://help.sap.com/saphelp_nw04s/helpdata/en/b2/e50138fede083de10000009b38f8cf/frameset.htm

Hope this helps you,

Regards,

Vinay

[Former Member]

Hi Peter,

Have a look at [Multi-Dimensional Modeling with BI|http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/6ce7b0a4-0b01-0010-52ac-a6e813c35a84?quicklink=index&overridelayout=true].

Regards,

Alexander