Does anyone know if it is possible to use a History Table in HANA for tables that are created and provisioned through SLT?
From this link it is pretty clear that you can alter an existing table (that might have been created by SLT) so that seems pretty straightforward. However, it seems that the complication might be the requirement to turn auto-commit off. In this case, would SLT be issuing the commit statement or how would that work?
Hi Lars, the use case would be the ability to model (or report) based on time dependent master data, or more specifically a Slowly Changing Dimension of Type 2. In BW speak, it would be accomplished through time dependent master data InfoObjects. The main functional questions might be "What is the YTD sales for [customer attribute] that was valid on [given date]. The only possible option I see (at this time) in HANA to solve this would be the history table.
So I know HANA supports this, but the main question is would it be supported with replicated tables using SLT?
SAP HANA supports to keep track of committed row changes.
This is a form of technical history and in your case with save a changed row when SLT committed it to SAP HANA and not when the record (note: record and row are semantically two different things!) had become valid from the business point of view.
Therefore, the history table feature does not cover your requirement here.
To enable time dependent master data, you still need to model this directly into your table design. Just like SAP BW on HANA still does it.
This is also further supported by features like the time dependent join in Analytic Views. There you select a column indicating record validity in the fact table and the valid-from and valid-to columns in the master data table to specify the join condition.
I know that at first sight the history table feature looks very appealing, but as mentioned in the blog discussion you referred it's not all that easy ...
Thanks for the insight Lars, after thinking about this and testing in BW I have a couple more points.
- "record and row are semantically two different things" - I understand what you mean here. The commit ID simply indicates when the change to a row was executed on the DB/when the record was received. This may not correlate to anything from a business point of view and does not equate to any functional time dependencies (valid to/valid from) in the data.
- In BW, when master data is made time dependent, there are two columns (valid to and valid from) that are added in order to aid in the temporal join functionality.
- There are only a few DataSources (that I know of) and a handful of ECC tables that naturally have the valid-to and valid-from information stored in them, which I would consider the "business point of view validity". In order to introduce time dependency functionality to a source of data that is not naturally time dependent, the only option is force this by loading the latest data with a timestamp into the future (see reference), which is more or less a "technical" validity approach similar to using timestamps, described below.
- For the "technical" approach for sources that are not naturally time dependent, BW handles the logic of moving around validity periods when it sees an overlapping section. For example, the first time a row is loaded it may have color "BLUE" valid from today to 12/31/9999, accomplished by passing valid from = sy-datum and valid to = 12/31/9999 constant. The next time the row is loaded at a later date, it may now be "RED" and is passed the similar values as above. When BW interprets the changes, it would look something like this (assumes the same natural primary key)
First Load
NULL 01/01/1000 - 07/16/2013
BLUE 07/17/2013 - 12/31/9999
Second Load
NULL 01/01/1000 - 07/16/2013
BLUE 07/17/2013 - 07/25/2013
RED 07/26/2013 - 12/31/9999
So we can draw a couple points with regard to the above illustration and your feedback
- HANA history tables may inherently may satisfy a "technical" approach for time dependency when time dependency is not naturally available in a given source (think MARA/KNA1, etc).
- Aside from time travel, the other option would be to model the validity directly. The validity periods could be supplied naturally where available, or implemented technically as shown above.
- SLT would not be able to handle the logic of handling the direct model scenario, in the case where validity is not naturally available. In this case, DataServices would likely be required to first read the existing data and update/insert new validity periods as required, similar to the way BW handles it above.
Sorry for the lengthy response, but I am just trying to determine the proper approach/tool with regard to time dependency. Maybe someone else is also investigating the same.
Actually I don't get where you're heading with this.
SLT is a tool to get data from a source system into SAP HANA more or less in a straight way and with low latency.
Although it provides options to change/transform data before it gets written to the target it's a replication technology. It's not meant to enrich your data model with time dependency that isn't build in there.
BO Data Services on the other hand is a full fledged ETL tool that can change input data more or less arbitrarily.
From the scenario you draw it looks as if you try to replace the complex data staging/loading handling present in SAP BW by something much simpler and by sneaking in a kind of time-dependency through usage of the history table feature.
Why not being explicit about this extension of your data model?
Besides that, the history table feature is not actually reachable for the temporal join in analytic views. This is only available if the validity date is actually present in table columns.
Lars, thanks for entertaining my long-winded analysis
"From the scenario you draw it looks as if you try to replace the complex data staging/loading handling present in SAP BW by something much simpler and by sneaking in a kind of time-dependency through usage of the history table feature."
This is exactly what I am trying to do - introduce time dependency where it does not exist naturally (using a 'technical' time dependency function). The analysis trail simply leads to the conclusion that SLT + history table would not be the appropriate tool(s) for the job! Since there was not much info available on the same, I was just exploring the topic with regard to the above.
The path for this type of requirement would be an ETL tool (BODS) and modeling the validity dates directly.
Just FYI the project I'm on right now has both SLT and BODS. Some of the tables replicated from SLT need to store history in HANA but as you've noted, SLT doesn't have history preservation. What the team here has done (and I've done some work on) is to handle history preservation through stored procedures, triggered daily from BODS.
(Yes this is a bit cludgy - since BODS has ETL history preservation features - but reasons that I'm not privy to, the decision was made to maintain SLT replication and update history daily).
I believe if BODS is not part of the landscape you could still trigger daily procedure runs via a cron job... but maintenance gets a bit messy.
Nonetheless, if you'd like details on the procedure approach to handling history preservation let me know.
When you say you are storing history, are you referring to master data/slowly changing dimensions like I mentioned above or transaction data?
BODS is still on the table, so I would just like to explore all options. You can see my email shared on my profile if you wish to send via email, again it is very much appreciated!
I am talking about SCDs. On the current project we had a pretty good solution, only major downside is maintenance of triggers.
Now it looks like there's an even more elegant solution handled via SLT - treating deletes as updates and marking records accordingly.
How urgent is your need? The reason that I ask is that a colleague of mine will likely published a detailed doc in the near future with all the alternative approaches with pros and cons for handling slowly changing dimensions with an SLT (or hybrid SLT/BODS) landscape. So I could refer you to that, but if you'd like some specifics, sample SQL, etc, let me know - and I could get something thrown together within a week or so.
Jody, thanks for the udpate - much appreciated. Clearly I would be interested in the more standardized solution offered from SLT, so I would be very keen to see the details on that detailed doc you are mentioning.
We will be looking at some detailed design options on my current project in the next few weeks so i need to start getting some ideas together. If you could share whatever would be meaningful (SQL, colleague's draft document, etc), feel free to drop it to my profile email.
as follow up to your question I created a real life example of how you can do that using Hana Task Transformations. This would be a true SCD2 dataflow just like in Data Services but using Hana technology alone and supporting realtime and batch.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.