Features Of Changelog & Active Queue Of Standard Data Store Object (DSO) in BI 7.0 :
Business Scenario
Sometimes it is desirable to combine
data from different Data Sources before the same is stored into the Info
Cubes. Also, there are analyses that need access to the detailed data
than that found in the Cubes.
Types of DSO
- Standard Data Store Object (Ref. Fig. A)
- Data Store Object with Direct Update (Transactional ODS using 3.x)
- Write Optimized Data Store - BI 7.0
- Contains only active data table used to manage huge data loads
Motivation for DSO
• Consolidation & Cleansing
o A further motivation is the need for a place where data can be consolidated and cleansed. This is important when we upload data from completely different Source Systems.
o After consolidation and cleansing, data can be uploaded to Info Cubes.
• To store data on document level
• Overwrite capability of characteristics
o Not possible to overwrite data in Info Cube as whenever data is added to Info Cube, this data is aggregated. So data can be overwritten in DSO and this provides a significant capability to BW.
• Reporting
o Direct on document level data
o Drilldown from Info cube to document level
Architecture of Standard ODS / DSO (7.x)
"ODS Objects consist of three tables as shown in the architecture" - Source: SAP Docs
Fig.A - ODS Object Structure
The Transition: ODS Objects (3.X) to DSO (BI 7.0)
The
ODS consists of consolidated data from several Info Sources on a
detailed (document) level, in order to support the document analysis.
In
the context of the DSO, the PSA makes up the first level and the DSO
table makes up the second level of the DSO.
Therefore, the first level
consists of the transaction data from the source system, and the second
level consists of the consolidated data and data from several source
systems and Info Sources.
You can run this analysis directly on the
contents of the table, or run it from an Info Cube query into a query by
means of a drilldown.
Fig. B. Sample schema for Reporting using ODS Objects (using Update Rules & Transfer Rules) * Note: UR refers to Update Rules
Prior
to existence of DSO, decisions on granularity were based solely on data
in Info Cube.
Now Info Cube can be less granular with data held for a
longer period of time versus the DSO which can be very granular but hold
data for a shorter period of time.
Data from the ODS can be updated
into appropriate Info Cubes or other ODS Objects. Reporting on ODS can
be done with the OLAP processor or directly with an ODS query.
In this Fig. B, data from Data Source A and Data Source B is uploaded to a PSA. The PSA (Persistent Staging Area) corresponds to DSO.
From the PSA we have the possibility, via transfer
rules, to upload data to DSO.
The DSO is represented here as one layer,
but depending on the business scenario, BI DSO can be structured with
multiple levels.
Thus, the ODS objects offer data that are subject oriented, consolidated and integrated with respect to same process on different source systems.
After data has been stored, or
while the data is updated in the ODS, we have option of making technical
changes as well as data changes.
In the ODS, data is stored in a
de-normalized data structure.
Structure of ODS
While
transferring data from PSA to ODS objects, rules (Transfer Rules) can
be applied to clean records and transform them to company-wide standards
for characteristic values.
If it is meaningful at this stage, business
logic may also be applied (Update Rules).
Sample Scenario for a Standard DSO
Consider an example involving a Standard DSO in SAP BI 7.0.
Let's check flat file records, the key fields are customer and material and we have a duplicate record (Check Rec.2).
The 'Unique Data Records ‘option is unchecked which means it can expect duplicate records.
Figure C. Explains how records are captured in a DSO (Refer selected options below)
After update rule, Record 2 in PSA is overwritten as it has got same keys. It's overwritten with most recent record.
The key here is [M1000 | Customer A].
If we note the monitor entries, 3 records are transferred to update rules & two records are loaded in to Active Queue table.
This is because
we haven't activated request yet & that duplicate record for key in
DSO gets overwritten.
Note: Activation Queue can also be expressed as 'New Data' table
The key figures will have the overwrite option by default, additionally we have the summation option to suit certain scenarios and the characteristics will overwrite always.
Naming Conventions
• Tech. Name of New data / Activation queue table is always for customer objects - /bic 140 and for SAP objects - /bio140.
• Name of active data table /BIC/A100 and /BI0 for SAP.
• Name of change log table - The technical name is always /BIC/.
Once
we activate we will have two records in DSO's Active Data table.
The
Active Data table always has contains the semantic key (E.g. Customer & Material for instance)
Change Log
The Change Log table has 2 entries with the image N (stands for ‘New'). The technical key (REQID, DATAPACKETID, RECORDNUMBER) will be part of change log table. (Refer Fig. D)
Fig. D - Data is loaded to CL & ADT (Pl. refer Fig. A for more details)
Introducing a few changes, we get the following result as in Fig. E.
Fig. E - Changes Introduced from the Flat file is reflected on PSA to ADT & PSA to CL
Detailed Study on Change Logs
We will check Change log table to see how the deltas are handled.
The
records are from first request that is uniquely identified by technical
key (Request Number, Data packet number, Partition value of PSA and Data record number).
With the second request the change log table puts the before and after Image for the relevant records.
Fig. F - Study on the Change Log on how the Deltas are handled
In
this example Customer and Material has the before image with record
mode "X".
And also note that all key figures will be having "-" sign if
we opted to overwrite option & characteristics will be overwritten
always.
A new record (last row in the Fig. F) is added is with the status "N" as it's a new record.
Fig. G - Final Change Log Output
Record modes
Fig.H - Types of Record modes
The
record mode(s) that a particular data source uses for the delta
mechanism largely depends on the type of the extractor.
Ref. OSS notes
399739 for more details.
Work Scenario
Let's go
through a sample real time scenario.
In this example we will take the
Master data object Customer, Material with a few attributes for the
demonstration purpose.
Here we define a ODS / DSO as below where
material and customer is a key and the corresponding attributes as data
fields.
• ODS / DSO definition
• Definition of the transformation
• Flat file Loading
• Monitoring the Entries
• Monitoring Activation Queue
• Monitoring PSA data for comparison
• Checking Active Data Table
• Monitoring Change Log Table
• Displaying data in suitable Info provider (E.g. Flat File to PSA to DSO to Info Cube)
Note:
In SAP BI 7.0 the status data is written to active data table in
parallel while writing to Change log. This is an advantage of parallel
processes which can be customized globally or at object level in system
No comments:
Post a Comment