How can you do time dimensions and non cumulative KFs in HANA, without BW

[Former Member]

Dear all,

I am trying to model a simple stock movement scenario using only HANA. No BW. Modeling to also Including the provisioning of data.

I know this functionality exists in sap BW, but for now, that is not an option, and so, not in the scope of my question here,

I have a totally customized application in ERP writing to Z tables.

I want a user to use Lumira or something like that, to query a HANA view, and see a snapshot of stock movement based on the stock status and date.

The challenges that I can see are ...

Records may not exist on a particular date, for a stock at a status, however, the stock can still be counted for that date.

And

When retrieving records for a given time period, we need a non cumulative measure.

Thanks in advanced

[former_member184768]

Hi Glen,

One of the option is to persist the inventory snapshots in a custom table. You can join the Stock Movement table with the calendar and generate daily / weekly snapshots. Each snapshot can be generated with the reference of the previous snapshot and sum of movements during the snapshot period. If you decide to have the weekly snapshots, then each week snapshot would be snapshot for previous week + movements in that particular week.

For the daily snapshot, each snapshot can be generated with reference to the previous day snapshot and movements for the day. If there are no movement documents generated for a given date, the the previous snapshot can be copied over. But in this process, the no of records generated would be quite high.

You can also agree with the business users to store the snapshots for a specific time period (6 months - 1 year) and delete all the previous snapshots to reclaim the memory.

Another option could have been joining day level Calendar with the Stock movement (left outer join) and then summing up all the stock movement quantities to a particular date. But if you need to display the stock for a range of dates, then the join has to materialize high volume of data resulting in negative impact on the performance.

I'd recommend the snapshot option as the data is persisted and has very high performance for query.

Regards,

Ravi

[Former Member]

hi ravi,

i should have stated that i do not want any intermediate persisted data.

thanks

[Former Member]

Did you find a solution for this in the end? I'm curious, because my thought would be to either use BW, or to persist snapshots.

[lbreddemann]

Hi Glen

When you don't want to keep snapshots that's going to be difficult.

The SAP BW modelling is based on snapshots and movement information based on those snapshots.

If you don't have them, it's always necessary to compute the current state and save the movement based on that, which might impact your transaction performance quite a bit.

Technically it's all about providing a timestamp of the movement so that upon query time you can tell which movements had already been made since the last fix point.

With an increase of transactions the query response time will get bigger in this model.

Alternatively including more or less frequent reconciliation points from where you start fresh will allow for constant query response times.

Since reconciliations also happen on the user-side of inventory management, I don't see why this would be a negative aspect to have.

Also: once you have snapshots you do have the option to drop movements that occured before the snapshot and still don't loose track of the inventory development.

That might come in very handy, when the movements are not interesting anymore a few years from now, while the general inventory development still might provide insight for further planning.

- Lars

[Former Member]

Hey Lars,

That's what I understood. In reality, have you seen any customers implement inventory management from ERP using HANA, without using BW business content?

BW seems to solve this problem so well that I'm struggling to see why someone would build a custom data-mart for this.

John

[lbreddemann]

Actually no, I didn't see such an implementation yet.

The BW model here really is covering most of the ground properly, so re-inventing it can easily end up being a huge waste of time.

It's just one of these fundamental data designs that need to be implemented just correctly to provide the required flexibility, performance and well, correctness of data.

Guess it's the same with many other features, that are just not available on DBMS level, but on a DW level.

Which again shows: BW on HANA is there to stay .

- Lars

[rama_shankar3]

Good points guys - thanks

[Former Member]

What method work efficiently to persist snapshots in custom table?  HANA procedure , BODS or SDI？

[Former Member]

You would likely do a stored procedure.

But in reality, BW covers off this scenario well. Customers are not trying to build this by hand and reinvent BW.

[Former Member]

Thanks john, I hope customer have BW too, they intend to migrate traditional Data Warehouse to pure HANA landscape without BW , so I'm afraid we have to develop a lots of SP & BODS ETL jobs to handle many scenario like  SCDs, Snapshot table, report on Stable or Realtime Data etc.

[Former Member]

Honestly, this isn't my recommendation. If you want an EDW for which BW is a functional fit, recommend they use BW.

HANA Data Marts are awesome if you need a specific piece of functionality, like an agile datamart for sales reporting.