Step-by-Step guide of Modeling HANA views into BW in SAP BW 7.4 on HANA

[former_member223957]

About this Document

Business Case

This document is based on Sales Business case scenarios.

Purpose

This manual aims to help you to understand the modeling of HANA views into BW and reporting based on BW providers in SAP BW on HANA. This kind of processing is done in SAP BW on HANA. This manual also provides details of the steps involved in creating and modeling HANA views and migrating to BW and creating a report based BW providers. This document is created solely with the intention of sharing information.

Pre-requisites

Following are the pre-requisites for performing the tasks presented in this manual:

• SAP BW on HANA 7.4 installed on the server with packages sp4 (SAPKW74004).
• SAP BW on HANA 7.3.1 installed on the server with packages sp9 (SAPKW73109).
• SAP BW on HANA 7.3 installed on the server with packages sp10 (SAPKW73010).
• HANA database upgraded to version HANA SPS 06 and above.
• BW authorization and Analytic privileges.
• Knowledge of BO Objects.
• Knowledge of BO Data services.

Contents

1  Overview of Transient provider, Virtual provider and Composite provider in BW 7.4

     1.1 Transient Provider

     1.2 Composite Provider

     1.3 Virtual Provider

2  Source Data Extraction (using BO Data Services)

3  Source Data Extraction (LO Extraction)

4  Modeling BW Objects in BW 7.4

5  Modeling in HANA

6  Modeling in BW on HANA

     6.1 Transient Provider

     6.2 Composite Provider

     6.3 Virtual Provider

     6.4 BEx Query Designer

          6.4.1 Transient Provider

          6.4.2 Composite Provider

          6.4.3 Virtual Provider

7  Reporting in Web Intelligence

8  Conclusion

1  Overview of Transient provider, Virtual provider and Composite provider in BW 7.4

DSO and InfoCube in BW 7.4 are HANA-Optimized by default. This reduces the activation time and has a huge impact on the performance .Similarly modeling HANA Views in BW on HANA into Transient providers, Composite Providers and Virtual Providers also has a huge impact on performance if the underlying database is HANA.

1.1 Transient Provider

Key features

• Its metadata in BW is not persisted, but always generated at runtime.
• BEx Queries built on top can adapt to changes automatically as far as possible.
• Navigational attributes of an assigned Info Object can’t be used .
• It cannot be used in Multi provider.
• It can only be used in Composite Provider in order to merge with other InfoProvider.
• It is exposed to BEx and BI tools.

1.2 Composite Provider

• Its biggest advantage that it performs both union and joins operation which overcomes the limitation of Info Sets and Multi provider in BW .

1.3 Virtual Provider

Key features

• It offers a very flexible way of integrating data not stored in BW Objects into the consistent BW world.
• We can turn on Navigational Attributes for the Virtual Provider as usual. Then we can map the Navigational Attribute also to a field in the HANA model in the “Provider specific properties” – if you do not map it, the data comes from the Master data tables in BW.
• Virtual Provider can be transported as usual, it can be used in a Multi Provider and BEx Queries can be built on top, just like for any other Virtual Provider.
• The data read access at Query runtime is not via SQL, but it uses the same special Analytics API that we use to access data in InfoCubes, DSOs, and Multi Providers. This enables the BW Analytic Manager to push down operations to HANA instead of performing them in the application server.

2   Source Data Extraction (using BO Data Services)

We will now see the basic feature of Extraction and Loading from Flat file system to HANA using BO Data Services.

• Log in to BO Data Services with proper username and password.

• We create new Excel type format to load Sales Item data (VBAP Table).  

• Next we import schema and load the data .

• Next we create a new BW on HANA Data store that connects to HANA database.

• Next we create a batch job and template table in BW on HANA.

• Next we also mapped the fields in Query transform.

• We run a batch job by right clicking it.
• Next we see the data in the template table and import the table by right clicking it.

3  Source Data Extraction (LO Extraction)

We will now see the basic feature of Extraction for Sales Header data from EC6 system.

• Log in to EC6 with proper username and password.

• We will activate the data sources using a transaction code RSA5.

• Next we have to fill up the set up tables for the data sources. For doing this first we need to delete the contents that are already present in that setup tables.
• Go to the transaction code LBWG and select the application component whose content has to be deleted.

• To check whether the setup table is deleted or not go to the transaction code RSA3 and give the data source name and it will show Record 0 found which means data has been deleted from the setup table.
• Now using the transaction code LBWQ delete the extraction queue of the data source.
• Also delete the delta queue for the data sources using the transaction code RSA7.
• Next is to fill the setup table using the transaction code SBIW or OLI*BW where * denotes the application component

• Click on Logistics under Settings for Application-Specific Data Sources--> Managing Extract Structures --->Application-Specific Setup of Statistical Data.
• Now click on the execute button of Perform setup – Sales and do it for delivery.

• We will now see the set up table contents using transaction code RSA3.

4  Modeling BW Objects in BW 7.4

• We will locate the application component and replicate both the data sources.

• Next we will create the info package for the data sources 2LIS_11_VAHDR and activate it so that we can see all the data that are selected in the extract structure will be in the PSA table.

• Next we will create the DSO for data source and load all the data into it using the data transfer process and activate both the DSO.

5  Modeling in HANA

We will now see the basic steps of modeling in HANA.

• Log in to HANA Database via HANA Studio by clicking add system.

• Analytic view is created on item table (VBAP) replicated by BO Data Services.
• NOTE: Make sure to mention the measure type ‘Amount with Currency’ for measure attribute like for COST, NETVALUE, NETPRICE to avoid mismatch problem with the predefined Info Objects while creating transient or virtual provider.
• CURR is a currency field of Item table (VBAP).

6 Modeling in BW on HANA

We will now see the basic steps of modeling Data marts in BW on HANA.

6.1 Transient Provider

• Using T Code RSSDD_HM_PUBLISH, we create Analytic Index which is Transient Provider in BW on HANA .

• Using T Code RSSDD_LTIP, we see the transient provider that we have already created.

• Select the Analytic Index and click display.

• While creating it, mention the Info Area.

• Now we can mention the reference InfoObject for the attributes.
• Reference InfoObject are used to copy the metadata structure to attribute.

• NOTE: Reference InfoObject is not mandatory in transient provider.

6.2 Composite Provider

• Now we create a composite provider on Sales Header Data (HANA-Optimized DSO) and Sales Item Data (Transient provider).
• NOTE: We can model a Composite Provider either by using the T Code RSLIMO / RSLIMOBW or the Workspace Designer  

• We can use either UNION or JOIN operands in modeling of Composite Provider based on requirements which is an advantage over Multi Provider (only union) and Infoset (only join).
• Drag and Drop HANA-Optimized DSO from left panel to right panel and use binding type as "UNION”.
• Drag and Drop Transient provider from left panel to right panel and use binding type as "Inner JOIN”.
• NOTE: In composite provider, there has to be one provider with binding type as “UNION”.

6.3 Virtual Provider

• Next we create a virtual provider on HANA Analytic view.

• Select and click Virtual provider based on HANA Model.

• Mention the package name and view name.

• Next we click the Assign HANA Model Attributes button in upper right corner in order assign the attributes to Info Objects.  
• Select propose mapping checkbox and click continue  

• Select the required attributes and If not found then Click continue.

• For Manual mapping, right click the dimension and click provider specific-Info Objects properties.

• Similarly do the same for Key Dimensions.
• Note: Make sure measure attributes like COST, NETPRICE,NETVALUE have same data type with predefined Info Objects .

• Finally Virtual provider is created and we now check the data .

6.4 B Ex Query Designer

6.4.1  Transient Provider

• Next we create a B ex Query on Transient provider and report it using RSRT.

6.4.2 Composite Provider

• We also create a B ex Query on Composite provider and report it using RSRT.

6.4.3  Virtual Provider

• We also create a B ex Query on Virtual provider and report it using RSRT.

7  Reporting in Web Intelligence

We will now see basic steps of reporting on BEx query in Web Intelligence.

• We create an OLAP connection in a repository in Information Design Tool.

• Next we open a BI launch pad and then web intelligence .
• We select BEx Query as datasource and then the required OLAP connection and then required Query.

• We also selected the required objects and run the query.
• We can also preview the data using refresh in data preview tab.

• We also created column chart along with tabular display of data for composite provider Query.

• We also created column chart along with tabular display of data for Virtual provider Query.
• We can also save the report as a different file like pdf, excel.

8  Conclusion

The concept of the Transient Provider has the biggest appeal in the context of ad-hoc models with frequent changes and a limited durability. But we can’t transport a Transient Provider and so we have to publish the HANA model in each system where we want to use it.

For the more long term and stable requirements we instead propose the new Virtual Provider, based on HANA model.

So as a general recommendation we state, that we should keep the model in HANA as simple as possible and model the calculations instead in the BEx Query.

[Ram7]

This is good go-through. Thanks for this utile content sharing.

[former_member182196]

Very nice information :smile: Nicely explorered  :smile: Thanks for sharing with us keep sharing new document :smile: .

Best Regards,

SM

[former_member185177]

Good one.  Thanks for sharing.

Regards,

Krishna Chaitanya.

[g_bot3]

Thanks for the info. Very usefull stuff.

One remark on the transient provider section. The transaction mentioned to create the TP is now RSDD_HM_PUBLISH, with one S at the start.

[former_member223957]

Hi G. Bot

I guess that the T code that you have mentioned for creating TP is  "SRSDD_HM_PUBLISH'. I am sorry if I have interpreted the T code wrongly .

But this T Code dose not exit in BW 7.4 SP 4 and BW  7.4 SP6 . Can you be more specific about the version that you have used .Please paste a screenshot in support of your query.

[Ram7]

Hello Mohammad,

I hope what G. Bot was pointed is not 'SRSDD_HM_PUBLISH' to create a TP we have to use the T Code 'RSDD_HM_PUBLISH' but here it is mentioned as 'RSSDD_****' Please correct me if wrong.

So, to create a TP we have to use 'RSDD_HM_PUBLISH'

[g_bot3]

Hi Ramakrishan,

You are right, I mean indeed that the tcode should be 'RSDD_HM_PUBLISH'.

[former_member223957]

Hi Ramakrishan,

Thank you Ramakrishan. Actually by mistake I have written that in the document(point  6.1)  .Just now I have figured it out .It should be RSDD_HM_PUBLISH.

Thank you everyone for going through the document . :smile:

[former_member223964]

Hi,

Nice one thanks for sharing.

regards,

Manikanta

[Former Member]

Hi

nice doccument,

i have requirement like, hierachy has to be created in HANA system.

for that i have loaded Hierarchy table in HANA system.

Now i'd like to access those table in BW since we are using BW 7.4

the table structure would be

NODEID  IOBJNAME NODENAME LINK PARENTID TXTSH TXTMD TXTLG

Now created Attribute view for the same table.

in BW side while trying to access the view, we can't see the View only Analytic and Cal views only visable.

but to create those views we need Measures to be mainitained.

but we have only attributes only.

could you please provide solution how to access those views in BW and how to procced with hierarchy in BW side.

Regards

Raj

[former_member223957]

Hi Rajesh,

For attribute views or hierarchy based attribute views  , we can access the views  in BW via master infoobject .You can see the option for importing attribute views in BW in infoobject .

thanks & regards

Mohammad

[Former Member]

Hi Mohmamad,

thanks for the post really helpful.

My requirement is in HANA view have created Analytic view based out table fileds

NODEID     NODENAME IOBJNAME     LANG PARENTID     SHR     MD     LAND

this the table which is Hierarchy loading into BW.

in BW we have created Virtualprovider to import the HANA View into BW.

then created Multiprovider to bring same Virtual provider into Multiprovider.

Diemension we have same attributes called NODEID NODENAME IOBJNAME .... etc.

now we have to map this in BW how's does this happens ?

since we have infoobject ZXXX made as a hierarchy object  in BW.

how would i map this Hierarchy object with  Multiprovider diemensions.

please help on this

Regards

Raj

[sricharani_kondasani]

Great Document. Please add more versions to this document. It really Helps :smile:

[o_baer]

Mohammad,

do you know whether TransientProvider and VirtualProvider also exist in a BW w/o Hana but any other DB? And eventually, linking these objects to any other Hana DB (e.g. ERP, sidecar etc.)?

Best regards,

Oliver.

[Former Member]

Hi Oliver,

Answer to your first question:

When you refer to transient provider and Virtual provider based on HANA models then in that case these two objects cannot excists on BW on traditional DB.

Answer to your second Question:

You cannot link the HANA artifacts in external HANA DB to another BW systems via Transient and virtual provider.

Thanks & Regards

    A.Dinesh

[former_member223957]

Hi Oliver ,

Thank you for reading the document .

Transient provider does not exist in a BW w/o HANA. Below screen shot is from BW 7.4 w/o HANA .

Where as Virtual Provider exist in BW w/o HANA .Virtual provider fetches the data at run time and does not physically stores the data i.e real time access (which is same as transient provider) but VP  has advantage over TP as VP uses the property of standard or custom info object like hierarchies etc 

But regarding the second question , it is not possible .It is only possible in BW on HANA as there is a option "based on HANA model"  in virtual provider .

I hope I have answered your queries .

[o_baer]

Thanks for the feedback. Although I don't like the answer for VPs being not able to connect to a secondary Hana DB 😉

[Former Member]

Hi Oliver Baer,

        I will try to make you to like the way VP behaves :smile: ... plz go through my explaination below:

we have to little understand the architecture of VP, virtual provider as such doesn't hold any data so it will be easy and performance benificial only if the data resides within the same HANA box if its exrenal then the virtual provider architecture has to change .SAP  dont like to change it because for accessing the external HANA object there is already an object named as ODS.So if your use case you have to use teh open ODS not VP.

I hope u r happy now :smile: ?

Thanks & Regards

    A.Dinesh

[RenjithKumar]

Hi mohammad.safiullah,

This is very good document. Do write more on these new topics :smile: .

Regards
Renjith
BW Forum moderator.

[Former Member]

It's very useful document..Thanks for sharing

[Former Member]

This document is very informative and really good for beginners who are trying to explore the the new InfoProviders in BW.

One question can the composit provider consume the virtual provider?

Regards,

Anupama

[former_member223957]

Thanks everyone for reading the document . :smile:

Hi Anupama ,

Yes ..Composite provider can consume the virtual provider .

[former_member216433]

Hi Safi

Excellent document,  This cleared my doubts on SAP BW/HANA.

Thanks

SV

[Former Member]

Hi Safi,

Good document.

[Former Member]

Hi Safi,

Good Document.

Thanks

hari

[Loed]

Hi,

Thanks a lot for sharing.. :smile:

Loed

[Former Member]

Hi MS,

Well documented.

Surprised to know that the transient providers can't be transported.

Why is it so.

Is there any logic given by sap behind this limitation?

As the transient provider meta data is stored in its own tables (around 5) starting with RSLTIP*

Can these table entries be transported and with this, whether these objects will get transported to the target system as these 5 tables have all the required details (metadata) of each and every transient provider.

I Understand that these transient providers are created when to be used on adhoc basis which means that it makes sense only when created directly in production as the doc says that these are not transportable. I don't think that this kind of creation access will be provided in production environment but if needed it can be provided and this is not some thing possible but my view is of following best practices.

All are welcome to share their views on the transport limitation with transient provider.

Regards

Kumar.

[Former Member]

Hi mohammad.safiullah,

Could you please tell me where could I find TransientProviders (2O0FIN_OPA_T50 and 2O0FIN_OPA_T20) which included in the Multi Provider /ERP/OPA_M01 as we have a system BI client installed on ECC and the data base is HDB...

I have checked Analytic Indexes using transaction RSDD_LTIP but I could not find any here.

Also have gone through some posts on this...as we could not find TPs in BW then where could we find them and if not in the system where could we check SAP provided TPs.

Thanks in advance..!!

Regards,

Neeraja.

[Former Member]

Hi,

     When u say Transient provider did you mean HANA model based transient provider? if yes the can you try checking it in the transcation rsdd_hm_publish .

Thanks & Regards

  A.DInesh

[former_member185071]

Great one.. really helpful..

Cheers

Kishore Madireddy

***Even if you have nothing, you can get anything. But your attitude & approach should be positive..!****

[changrun_lin]

great article. Well explained. Thank you!

[sat_p2]

good doc

[former_member223795]

Nice Document.

Regards,

Shaiksha Vali. K

[Former Member]

Nice Document.

I need a help. I am ABAP consultant so should I go for SAP-HANA? and how it will help me to learn.

And again i heard HANA is preferable for BW consultant.

please suggest.

Regards,

Sunil

[Former Member]

Good directional content for beginners! Thanks for sharing.

[ashutosh_shrivastava4]

Excellent...

[Former Member]

Hi Experts,

Quick Question:

While doing data display for Virtual Provider created based on HANA Calc View, i'm getting below error:

User is not authorized; Cannot add AP runtime filters; Query ID<>

Error 2,950 occured in BWA/HANA Server

Error reading data of Infoprovider

 

Thanks,

Jomy

[VijayMutyala]

Hi,

Nice Explanation. i have a doubt about Transient Provider. how do we transport them to other systems like Quality & Production because these are non transportable objects & to transport we need to collect the Information from RSLTIP* database tables into Transport Request and the release and import in other systems this is the process how exactly we do follow when we dealing with Transport Provider. i know this but actually my question is how do we connect that imported Transient Provider with Backend Hana Models?

Please someone  should answer this Question.

Thanks,

VijayMutyala.

[ram_makkena]

Hi All,

I have created a calculation view and I would like to create a virtual provider on top it in BW. My view is getting proper data and while creating virtual provider in BW, I am not able to see this view or package in which I have created this view.  My question is do we need to create any connection between HANA views to BW?

 

I checked with the authorization and it is the superuser having full rights. Can someone please help me here.

 

Thanks in advance.

 

Regards,

Ram M