SAP Blog
Compressing Snapshots in Data Services

Compressing Snapshots in Data Services

Sometimes, when capturing data in a data warehouse, we need to store time-variant pieces of data about a transaction. This somewhat blurs the lines between a traditional fact table and a dimension, since in the traditional model, time-variance is mainly the domain of a dimension.

Take the example of a production backlog at a manufacturer. When an order is made, particularly in an organization that manufactures large and/or complex goods, it may take some time to fulfill. Maintaining a consistent backlog is also a key to ensuring consistent production planning that’s not beset with shutdowns, inefficiencies, or missed delivery dates.

Keeping a backlog at a granular level generally requires tracking backlog on an order-by-order basis. That way, anything about an order (that’s in your warehouse) can be analyzed to look for trends in the business. There’s just one issue: keeping a snapshot of every order in backlog for the full amount of time it’s in backlog can take up a lot of space. For instance in a mid-size company: if the average order is in backlog for three months and the company receives 10,000 orders per year, that’s nearly a million records per year in a daily snapshot. After a while, that can really add up. It’s no wonder Bill Inmon said, “The management of these every day, ongoing changes can consume an enormous amount of resources and can be very, very complex. It is this type of load that rivets the attention of the data architect.” (Snapshots in the Data Warehouse, pg. 2, white paper at


This example could also apply to general A/R snapshots by account, though in many organizations, this snapshot is taken on a monthly basis, so the problem is less imperative.

As the snapshot grows, a simplified version may look something like this:

Order Key Date Key Item Key Past Due Backlog Amount Future Backlog Amount
50 20131201 76 0.00 65,000.00
50 20131202 – 20131231
50 20140101 76 35,000.00 30,000.00
50 20140102 76 0.00 30,000.00
50 20140103 – 20140114 .. .. ..

In this example, an order for $65,000.00 was placed on December 1, 2013. $35,000.00 worth of product is for delivery on December 31, while $30,000.00 worth of product is scheduled for later. So, all $65,000 goes into future backlog when the order is received.

To download PDF and Continue Reading…

Britton HeadshotAbout Britton Gray
Britton has been working in software development and Business Intelligence for the past fourteen years, and has been working with SAP BusinessObjects reporting, analytics, and information management tools for six years. He is certified in Data Integrator and Web Intelligence, and enjoys developing end-to-end BI solutions that enable customers to gain new insights into their data.


Add comment

  • Hi,

    I really like the details on BODS. I m facing an issue for last 6 months in my DS jobs that are extracting data from SAP via ABAP R/3 dataflow ( abap program) , the job uses a SAP ID to connect to ECC ( the ID , PWD is set in the R/3 datastore) , that ID is getting locked at any point of time suddenly. We checked noone is using that ID by giving wrong password and even all the jobs datastore is set properly using a valid pwd only. So, whenever it gets locked , we check with security team , they unlock the same and then again manually we need to restart the DS jobs.

    Is there any solution to this problem ? Need your suggestion ac

    • If they have verified that the account is correct in the data store in the run repository then that is all that is required to have Data Services create the ABAP and push it down to ECC. Works just like SQL, just a different language to a different target system.

      My guess is that another process with a legacy password is occurring from another application or job. I would run an SAP trace or investigate in logs to see if you can isolate if they are coming from the BODS server. Check all Data Store connections to ensure that their passwords are correct because Data Services will go out and “Query” or test all connections that are specified in the job. For example if they have reference to a function in another referenced R/3 or SAP Data Store, then those will be tested as well. Any that have a wrong password could lock the account.