Dimension design: A different perspectiveObjective: The objective of this post is to simplify the understanding on dimension designs of an infocube and to decide upon the dimensions based on the repetition of the data held in the dimension tables.

Pre-requisites: An infocube is already created and active, and filled will data, which will be used for analysis of dimension tables.

Dimension to Fact Ratio Computation: This ratio is a percentage figure of the number of records that exists in the dimension table to the number of records in fact table or what percentage of fact table size is a dimension table. Mathematically putting it down, the equation would be as below:

          Ratio = No of rows in Dimension table X 100 / No of rows in Fact Table

Dimension Table Design Concept: We have been reading and hearing over and over again that the characteristics should be added into a dimension if there exists a 1:1 or 1:M relation and they should be in separate dimension if there exists a M:M relation. What is this 1:1 or 1: M? This is the relation which the characteristics share among each other.

For instance if one Plant can have only one Storage Location and one storage location can belong to only one plant at any given point of time, then the relation shared among them is 1:1.

If 1 Functional Location can have many equipment but one equipment can belong to only one functional location then the relation shared between the functional location and Equipment is 1:M.

If 1 sales order can have many materials and one material can exist in different sales orders then there absolutely is no dependence among these two and the relation between these two is many to many or M: M.

Challenges in understanding the relationship: Often we SAP BI consultants depend on the Functional consultants to help us out with the relationship shared between these characteristics / fields. Due to time constraint we generally cannot dedicate time to educate the functional consultants on the purpose of this exercise, and it takes a lot of time to understand this relationship thoroughly.

Scenario: An infocube ZPFANALYSIS had few dimensions which were way larger than the preferred 20% ratio. This had to be redesigned such that the performance was under 20% ratio.

This ratio could be either manually derived by checking the number of entries in the desired dimension table (/BIC/D<infocube name><dimension number>) to the fact table (/BIC/F<Infocube Name> or /BIC/E<Infocube name>) or a program SAP_INFOCUBE_DESIGNS can be executed in SE38 which reports this ratio for all the dimensions, for all the infocubes in the system.

SAP_INFOCUBE_DESIGNS:

We can find from the report that the total number of rows in the fact table is 643850. Dimension 2 (/BIC/DZPFANLSYS2) has around 640430 rows, which is 99% (99.49%)of the fact table rows and Dimension 4(/BIC/DZPFANLSYS4) has around 196250 rows, which is 30%  (30.48%)of the fact table rows.

Infocube ZPFANLSYS:

Approach:

Step 1: Analysis of the dimension table /BIC/DZPFANALSYS2 to plan on reducing the number of records.

/BIC/DZPFANLSYS2

Fact table:

Dimension table holds 1 record more than the fact table.

View the data in the table /BIC/DZPFANLSYS2 (Table related to Dimension 2) in SE12 and sort all the fields. This sorting will help us spot the rows which have repeated values for many columns, which will eventually lead to understanding the relationship between the characteristics (columns in dimension table).

Identifying the relationships:

Once the sorting is done we need to look out for the number of values that repeat across the columns. All the records which repeat could have been displayed in a single row with one dimension id assigned if all the columns had same data. The repetition is a result of one or more columns which contribute a unique value to each row. Such columns if removed from the table then the number of rows in the table will come down.

In the below screenshot I’ve highlighted the rows in green that were repeating themselves with new dimension IDs, as only 2 columns SID_ZABNUM and SID_0NPLDA have new values for every row. These two columns having new values for every row have resulted in rest of the columns repeating themselves and in turn increasing the data size in the dimension table. Hence it can be easily said that these two columns do not belong in this dimension tables, so the related characteristics (ZABNUM and 0NPLDA) need to be removed out of this dimension.
Few rows could be found which repeat themselves for most of the columns, but have a new value once in a while for some columns, as highlighted in yellow in the below screenshot. This indicates that these columns share a 1:M relation with the rest of the columns with repeated rows and these could be left in the same dimension.

Conclusion: The columns marked in green belong to this dimension tables and the columns marked in red needs to be in other dimension tables.

Step 2: Create a copy infocube C_ZPFAN and create new dimensions to accommodate ZABNUM and 0NPLDA.

ZABNUM was added to dimension C_ZPFAN8 and 0NPLDA was added to C_ZPFAN7. These were marked as line item dimensions as they have only one characteristic under them.

Analysed the issue with dimension 4 in the similar way and changed other dimensions to help the situation.

Post changes, loaded the data into the copy infocube C_ZPFAN and found the number of records in the dimension table /BIC/DC_ZPFAN2 to be 40286.

Ratio: 40286 / 657400 * 100 = 6.12 %

SAP_INFOCUBE_DESIGNS:

Dimension2 of the copy infocube: /BIC/DC_ZPFAN2

Even now there a few repeated rows and columns, but the ratio is within 20%. We can create up to 13 dimensions, but it is always better to keep a dimension or two free for future enhancements.

Hope this was helpful.

SAP BI Interview Questions Cont2....

What is table partition?
A: SAP is using fact table partitioning to improve the performance. you can partition only on 0CALMONTH or 0FISCPER
How would you convert a info package group into a process chain?
A: Double Click on the info package grp, click on the ‘Process Chain Maint’ button and type in the name and descrition ; the individual info packages are inserted automatically.
How do you replace a query result from a master query to a child query?
A: If you select characterstic value with replacement path then it used the results from previuos query; for ex: let us assume that u have query Q1 which displaysthe top 10 customers, we have query Q2 which gets the top 10 customers for info object 0customer with as a vairable with replacement path and display detailed report on the customers list passed from Q1.
What is modeling?
It is an art of designing the data base. The design of DB depends on the schema and the schema is defined as representation of tables and their relationships.
What is an info cube?
Info cube is structured as star schema (extended) where a fact table is surrounded by different dim table that are linked with DIM’ids. And the data wise, you will have aggregated data in the cubes.
What is extended star schema?
In Extended Star Schema, under the BW star schema model, the dimension table does not contain master data. But it is stored externally in the master data tables (texts, attributes, hierarchies).
The characteristic in the dimensional table points to the relevant master data by the use of SID table. The SID table points to characteristics attribute texts and hierarchies.
This multistep navigational task adds extra overhead when executing a query. However the benefit of this model is that all fact tables (info cubes) share common master data tables between several info cubes.
Moreover the SID table concept allows users to implement multi languages and multi hierarchy OLAP environments. And also it supports slowly changing dimension.
delete a BEx query that is in Production system through request.
A) Using the RSZDELETE transaction
How would you optimize the dimensions?
• We should define as many dimensions as possible and we have to take care that no single dimension crosses more than 20% of the fact table size.
What are Conversion Routines for units and currencies in the update rule?
• Using this option we can write ABAP code for Units / Currencies conversion. If we enable this flag then unit of Key Figure appears in the ABAP code as an additional parameter. For example, we can convert units in Pounds to Kilos.
Can an InfoObject be an InfoProvider, how and why?
• Yes, when we want to report on Characteristics or Master Data. We have to right click on the InfoArea and select “Insert characteristic as data target”. For example, we can make 0CUSTOMER as an InfoProvider and report on it.
What is Open Hub Service?
• The Open Hub Service enables us to distribute data from an SAP BW system into external Data Marts, analytical applications, and other applications. We can ensure controlled distribution using several systems. The central object for exporting data is the InfoSpoke. We can define the source and the target object for the data. BW becomes a hub of an enterprise data warehouse. The distribution of data becomes clear through central monitoring from the distribution status in the BW system.
How do you transform Open Hub Data?
• Using BADI we can transform Open Hub Data according to the destination requirement.
What is ODS?
• Operational DataSource is used for detailed storage of data. We can overwrite data in the ODS. The data is stored in transparent tables.
What are BW Statistics and what is its use?
• They are group of Business Content InfoCubes which are used to measure performance for Query and Load Monitoring. It also shows the usage of aggregates, OLAP and Warehouse management.
What are the steps to extract data from R/3?
• Replicate DataSources
• Assign InfoSources
• Maintain Communication Structure and Transfer rules
• Create and InfoPackage
• Load Data
What are the delta options available when you load from flat file?
• The 3 options for Delta Management with Flat Files:
o Full Upload
o New Status for Changed records (ODS Object only)
o Additive Delta (ODS Object & InfoCube)
What are the extractor types?
• Application Specific
o BW Content FI, HR, CO, SAP CRM, LO Cockpit
o Customer-Generated Extractors
LIS, FI-SL, CO-PA
• Cross Application (Generic Extractors)
o DB View, InfoSet, Function Module
What are the steps involved in LO Extraction?
• The steps are:
o RSA5 Select the DataSources
o LBWE Maintain DataSources and Activate Extract Structures
o LBWG Delete Setup Tables
o 0LI*BW Setup tables
o RSA3 Check extraction and the data in Setup tables
o LBWQ Check the extraction queue
o LBWF Log for LO Extract Structures
o RSA7 BW Delta Queue Monitor
How to create a connection with LIS InfoStructures?
• LBW0 Connecting LIS InfoStructures to BW
What is the difference between ODS and InfoCube and MultiProvider?
• ODS: Provides granular data, allows overwrite and data is in transparent tables, ideal for drilldown and RRI.
• CUBE: Follows the star schema, we can only append data, ideal for primary reporting.
• MultiProvider: Does not have physical data. It allows to access data from different InfoProviders (Cube, ODS, InfoObject). It is also preferred for reporting.
What are Start routines, Transfer routines and Update routines?
• Start Routines: The start routine is run for each DataPackage after the data has been written to the PSA and before the transfer rules have been executed. It allows complex computations for a key figure or a characteristic. It has no return value. Its purpose is to execute preliminary calculations and to store them in global DataStructures. This structure or table can be accessed in the other routines. The entire DataPackage in the transfer structure format is used as a parameter for the routine.
• Transfer / Update Routines: They are defined at the InfoObject level. It is like the Start Routine. It is independent of the DataSource. We can use this to define Global Data and Global Checks.
What is the difference between start routine and update routine, when, how and why are they called?
• Start routine can be used to access InfoPackage while update routines are used while updating the Data Targets.
What is Star Schema?
In Star Schema model, Fact table is surrounded by dimensional tables. Fact table is usually very large, that means it contains millions to billions of records. On the other hand dimensional tables are very small. Hence they contain a few thousands to few million records. In practice, Fact table holds transactional data and dimensional table holds master data.
The dimensional tables are specific to a fact table. This means that dimensional tables are not shared to across other fact tables. When other fact table such as a product needs the same product dimension data another dimension table that is specific to a new fact table is needed.
This situation creates data management problems such as master data redundancy because the very same product is duplicated in several dimensional tables instead of sharing from one single master data table. This problem can be solved in extended star schema.
What is slowly changing dimension?
Dimensions those changes with time are called slowly changing dimension.
What is fact table?
Fact table is the collection if facts and relations that means foreign keys with the dimension. Actually fact table holds transactional data.
What is dimension table?
Dimension table is a collection of logically related descriptive attributes that means characteristics.
How many tables does info cube contain?
Actually info cube contains two tables’ E table and F (fact) table.
What is the maximum no. of dimensions in info cube?
16(3 are sap defines and 13 are customer defined)
What are the minimum no of dimensions in info cube?
4(3 Sap defined and 1 customer defined).
What are the 3SAP defined dimensions?
The 3 SAP defined dimensions are…..
1. Data packet dimension (P)…..it contains 3characteristics.a) request Id (b) Record type (c) Change run id
2. Time dimension (T)….it contains time characteristics such as 0calmonth, 0calday etc
3. Unit Dimension (U)…it contains basically amount and quantity related units.
What is the maximum no. of key figures?
233
What is the maximum no. of characteristics?
248
What is the model of the info cube?
Info cube model is extended star schema.
What are the data types for the characteristic info object?
There are 4types:
1. CHAR
2. NUMC
3. DATS
4. TIMS
How you’ll write date in BW?
YYYYMMDD

The below 25 points are the small tips to increase your query performance. You can use them as a checklist while creating a Query. Here it goes..

1. If exclusions exist, make sure they exist in the global filter area. Try to remove exclusions by subtracting out inclusions.
2. Use Constant Selection to ignore filters in order to move more filters to the global filter area. (Use ABAPer to test and validate that this ensures better code)
3. Within structures, make sure the filter order exists with the highest level filter first.
4. Check code for all exit variables used in a report.
5. Move Time restrictions to a global filter whenever possible.
6. Within structures, use user exit variables to calculate things like QTD, YTD. This should generate better code than using overlapping restrictions to achieve the same thing. (Use ABAPer to test and validate that this ensures better code).
7. When queries are written on multiproviders, restrict to InfoProvider in global filter whenever possible. MultiProvider (MultiCube) queries require additional database table joins to read data compared to those queries against standard InfoCubes (InfoProviders), and you should therefore hardcode the infoprovider in the global filter whenever possible to eliminate this problem.
8. Move all global calculated and restricted key figures to local as to analyze any filters that can be removed and moved to the global definition in a query. Then you can change the calculated key figure and go back to utilizing the global calculated key figure if desired
9. If Alternative UOM solution is used, turn off query cache.
10. Set read mode of query based on static or dynamic. Reading data during navigation minimizes the impact on the R/3 database and application server resources because only data that the user requires will be retrieved. For queries involving large hierarchies with many nodes, it would be wise to select Read data during navigation and when expanding the hierarchy option to avoid reading data for the hierarchy nodes that are not expanded. Reserve the Read all data mode for special queries---for instance, when a majority of the users need a given query to slice and dice against all dimensions, or when the data is needed for data mining. This mode places heavy demand on database and memory resources and might impact other SAP BW processes and tasks.
11. Turn off formatting and results rows to minimize Frontend time whenever possible.
12. Check for nested hierarchies. Always a bad idea.
13. If "Display as hierarchy" is being used, look for other options to remove it to increase performance.
14. Use Constant Selection instead of SUMCT and SUMGT within formulas.
15. Do review of order of restrictions in formulas. Do as many restrictions as you can before calculations. Try to avoid calculations before restrictions.
16. Check Sequential vs Parallel read on Multiproviders.
17. Turn off warning messages on queries.
18. Check to see if performance improves by removing text display (Use ABAPer to test and validate that this ensures better code).
19. Check to see where currency conversions are happening if they are used.
20. Check aggregation and exception aggregation on calculated key figures. Before aggregation is generally slower and should not be used unless explicitly needed.
21. Avoid Cell Editor use if at all possible.
22. Make sure queries are regenerated in production using RSRT after changes to statistics, consistency changes, or aggregates.
23. Within the free characteristics, filter on the least granular objects first and make sure those come first in the order.
24. Leverage characteristics or navigational attributes rather than hierarchies. Using a hierarchy requires reading temporary hierarchy tables and creates additional overhead compared to characteristics and navigational attributes. Therefore, characteristics or navigational attributes result in significantly better query performance than hierarchies, especially as the size of the hierarchy (e.g., the number of nodes and levels) and the complexity of the selection criteria increase.
25. If hierarchies are used, minimize the number of nodes to include in the query results. Including all nodes in the query results (even the ones that are not needed or blank) slows down the query processing. The "not assigned" nodes in the hierarchy should be filtered out, and you should use a variable to reduce the number of hierarchy nodes selected.

SAP BW BI Interview Questions Continued....

By: leela naveen

This are questions I faced. If u have any screen shots for any one of the question provide that one also.
1. We have standard info objects given in sap why you created zinfo objects can u tell me the business scenario
2. We have standard info cubes given in sap why you created zinfo cubes can u tell me the business scenario
3. In keyfigure what is meant by cumulative value, non cumulative value change and non cumulative value in and out flow.
4. when u creating infoobject it shows reference and template what is it
5. what is meant by compounding attribute tell me the scenario?
6. I have 3 cubes for that I created multiprovider and I created a report for that but I didn’t get data in that report what happen?
7. I have 10 cubes I created multiprovider I want only 1 cube data what u do?
8. what is meant by safety upper limit and safety lower limit in all the deltas tell me one by one for time stamp, calender day and numberic pointer?
9. I have 80 queries which query is taking so much time how can you solve it
10. In compression level all requests are becoming zero which data is compressing tell me detail
11. what is meant by flat aggregate?explain in detail
12. I created process chain 1st day it taking 10 min after that 1st week it taking 1 hour after that next time it taking 1 day with a same loads what happen how can u reduce the time of loading
13. how can u know the cube size? in detail show me u have screen shots
14. where can we find transport return codes
15. I have a report it taking so much time how can I rectify
16. what is offset? Without offset we create queries?
17. I told my process chains nearly 600 are there he asked me how can u monitor I told him I will see in rspcm and bwccms he asked is there any third party tools is there to see? Any tools are there to see tell me what it is
18. how client access the reports
19. I don’t have master data it will possible to load transaction data? it is possible is there any other steps to do that one
20. what is structure in reporting?
21. which object based you created extended star schema?
22. what is line item dimension tell me brief
23. what is high cardinality tell me brief
24. process chain is running I have to stop the process for 1 hour after that re runn the process where it is stopped?
in multiprovider can I use aggregations
25. what is direct schedule and what is meta chain
26. which patch u used presently? How can I know which patch that one?
27. how can we increase data packet size
28. hierarchies are not there in bi?why
29. remodeling is applied only on info cube? why not dso/ods?
30. In jump queries we can jump any transactions just like rsa1, sm37 etc it is possible or not?
31. why ods activation fail? What types of fails are there? What are the steps to handle
32. I have a process chain is running the infopackage get error don’t process the error of that info package and then you can run the dependent variants is it possible?

Give me any performance and loading issues or support issues
Reporting errors, Loading errors, process chain errors?

BI Certification Info links

Links and information about the certifications offered by SAP in the BI Space. Common questions regarding BI certification are addressed and relevant links provided.
There are lots of question floating arounds related to BI certification. This Wiki is for those who has already thought of taking the exam. If you are still
doubtfull, check here: https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/9941.


SAP provides three levels of Certification in BI.
1.C_TBW45_04S
Certification Syllabus SAP Consultant Certification Solution Consultant SAP NetWeaver 2004s - Business Intelligence
For more details, check here :http://www.sap.com/services/education/certification/certroles/certificationtest.epx?context=FFC760B8923D16BB5150DAE63E7C1A6B331AF0B9E3A8F73CE3A9B7046E051044503600C911DBA13DCE978D3AC9057626D2B68111A7CD2D707E2EEC31213097E46EB790DD0106435EE0756F7B22F3FA4B4FF0645C06954BF3A150E023B4164DA2C3943DE02E599735DB9B4334B30B38FF20A1DC779D8F55E5F7A6893BDBFA38B94CF455E2A3E0E6851014966C90C80E173937CF7C2372A6FC%7cDA891B3C877030D9765B85CAE6AC82FC3EB6BC7DA10B7335


2 . C_TBW45_70:- SAP Certified Application Associate- Business Intelligence with SAP NetWeaver 7.0
check here
http://www.sap.com/services/education/catalog/netweaver/certificationtest.epx?context=FFC760B8923D16BB5150DAE63E7C1A6B331AF0B9E3A8F73CE3A9B7046E051044503600C911DBA13DCE978D3AC9057626D2B68111A7CD2D707E2EEC31213097E46EB790DD0106435EE0756F7B22F3FA4B4FF0645C06954BF39AD9826DEEE08116%7cDA891B3C877030D9C4305205DF5BCE39


3. P_BIE_70 - SAP Certified Application Professional - BI Enterprise DataWarehousing with SAP Net Weaver 7.0
checkhere :
http://www.sap.com/services/education/certification/certificationrole.epx?context=%5b%5bROLE_P_BIE_70%5d%5d%7c

These certification exams vary in their level of difficulties and different pattern. For example:C_TBW45_04S will have 80 questions to be answerd in 3 hours whereas C_TBW45_70 will have 90question in 3 hours. Also,
you will get partial credit in the former but no such privilegesin later exam
.There are no better study materials than TBWs. Which all TBWs to be read, check the syallabus here:http://www.sap.com/services/education/certification/certificationtest.epx?context=%5b%5bC_TBW45_04S%5d%5d%7c


These can be downloaded from Internet. Besides these TBWs, there are some important links - which is listed below:
Simulation Test:
http://www.sapbw.info/SAP-BW-Certification-Test-Simulation.html
http://www.hometutorials.com/SAP-BW
Some Important Documents on SDN:
https://www.sdn.sap.com/irj/sdn/advancedsearch?query=+BI+7.0+documents+&cat=sdn_all
Threads Related to Certification:
1. https://www.sdn.sap.com/irj/sdn/thread?threadID=857655&tstart=0
2. https://www.sdn.sap.com/irj/sdn/thread?messageID=5582263#5582263
3. https://www.sdn.sap.com/irj/sdn/thread?messageID=6053753#6053753
Miscellaneous Links:
http://www.psimedia.ws/
http://www.sap.com/uk/services/education/courses/bw.epx
http://www50.sap.com/useducation/curriculum/print.asp?jc=1&rid=285
http://www50.sap.com/useducation/curriculum/print.asp?jc=1&rid=458
http://www50.sap.com/useducation/certification/examcontent.asp
http://www50.sap.com/useducation/certification/curriculum.asp?rid=506&vid=5
http://www50.sap.com/useducation/certification/curriculum.asp?rid=420
http://csc-studentweb.lrc.edu/swp/Berg/BB_index_main.htm
https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/c27a9990-0201-0010-a393-e6e8bed520fe
https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/79f6d190-0201-0010-ec8b-810a969028ec
https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/620b406d-0601-0010-3b9a-ac51c445860f
https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/5f243d6d-0601-0010-2aae-abe0a4dcfadb
https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/f0d16261-80c0-2910-149a-97b017a900e4
https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/20b0d390-0201-0010-408c-f27f82427e23
https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/9be229f6-0901-0010-8288-824675320301
https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/508d0387-001d-2a10-a394-faa4e57f6751