BI Design Guidelines
I am looking for some guidelines for building the Data Model, and developing a Query?
Example, what are the Dos and Donts.
1) Always have the MultiProvider build on the top of each model
1) All query must (as much as possbile) to be developed from MultiProvider
2) Query must not have a lot of Calculated Key Figure etc
Please advice, thank you.
Mr. V replied
A MultiProvider builds up a data union of basic InfoProviders.
The complete data of all basic InfoProviders are available for reporting. A MultiProvider is interpreted at runtime as independent BI queries on each basic InfoProvider where the results are merged into a single result set.
When the reporting scenario is to be extended, use a MultiProvider as central interface between query definition and basic InfoProviders. When another InfoProvider is added to the MultiProvider definition, the technical name of a query based on the MultiProvider remains unchanged.
Use a MultiProvider to reduce the size of the basic InfoProviders. Advantages: parallel access to underlying basic InfoProviders, load balancing, resource utilization, query pruning.
Make sure that your MultiProvider only retrieves data from relevant basic InfoProviders at query runtime by
u2022 Using constants in the design of the basic InfoProviders
u2022 Using different key figures in the design of the basic InfoProviders
u2022 Using characteristic 0INFOPROV when designing a query on the MultiProvider
Are you planning to use a MultiProvider? If so, you have to ensure that the characteristics you want to report exist in all basic InfoProviders.
Do not use more than one non-cumulative InfoCube (InfoCube with at least one non-cumulative key figure) because this could lead to incorrect query results.
Do not use calculations before aggregation on MultiProvider because this may lead to wrong query results.
Do not combine basic InfoProviders having inhomogeneous data models in a MultiProvider. Use the report-report interface between queries defined on the basic InfoProvider instead.
Avoid using only parts of compound characteristics in the constituent basic InfoProvider of a MultiProvider.
Use a multi-provider approach carefully.
If the multiprovider is made up of infocubes with the same data model, but the cubes cannot use structure-specific infoobject properties (that is, the cubes are made up of disjoint sets of data, where the partitioning characteristic has a range of values in each cube), then BW must query each cube in order to retrieve the result.
However, a homogenous multi-provider is useful if the underlying cubes have a constant value defined for a partitioning characteristic and queries restrict on that characteristicu2014as a result, only cubes that contain relevant data are accessed.
Therefore, it is recommended that you examine the multi-provider approach for your installation to ensure the optimizer can adequately determine which cubes to access. The result will be improved query performance.
To improve system performance, it is recommend to use MultiProviders in Business Content.
Every BasicCube has a MultiProvider to which the queries relate. This has the following advantages:
If a BasicCube contains too much data, you can simply create an identical BasicCube and link it to the existing MultiProvider. The advantage of this is that you do not have to change the design of the queries because they are on the MultiProvider and remain unaffected by the BasicCube change.
By using MultiProviders, you can partition the BasicCubes better into particular data retention time periods. Using constants, you can make a setting in a BasicCube, for example, so that the only calendar year available is 2002. This can significantly improve the performance of reporting.
The MultiProvider concept allows you to partition data technically using time characteristics and logically by characteristic.