Load flat file decimal values to ADSO (SAP BI) and access in SAP HANA Cal view ( eclipse/H Studio)

We had a requirement to load GAS emission data into SAP BI, as the business does a lot of manual calculations in excel, to get the emission factor which is dependent on multiple values. They first need to sum up the values, do a standard deviations, SQRT of some NO2, Co2, IC4 values, divide it by some factor etc., which is a heavy excel based lookups and calcuations.

I have automated this manual excel based calculations in SAP HANA, by loading the flat files into SAP BI through SAP ADSO. As this data is fetched from an external 3rd party agency, there is no way to connect to their system and they send these excel files to the client.

I bet there are multiple links/blogs to show the flat file loading in a DSO by creating a flat file datasource and extract structure. I don’t want to repeat it again. But want to mention the main key points where I had challenges in loading the data to SAP HANA. Its like summarising the tips so that it will be easy for anyone to go through the below steps. Its not a step by step tutorial to do the datasource creation.

The top issue is the formatting as the excel file has a huge decimal calculations and need to get the same decimal values loaded to BI.

TROUBLE SHOOTING TOP TIPS:

◉ If the data type is NUMC negatives will not be read in the PSA, hence use DEC and format = Internal in the extract structure to make sure the same is reflected in BI. The file format has decimal values and the same is needed in BI, so create a decimal field in the adso with DEC data type and 15 length and scale as 3.

◉ Similar to below, if you are using SAP HANA, field level modeling, no need to create Info objects in the ADSO. It’s very simple, just create a field level ADSO and the data can be used in SAP HANA – cal views. This can be used it through a composite provider or this cal view can be directly accessed in SAP Webi.

◉ If required, create a SAP BW query on top of the composite provider and access this in SAP Webi – formulas/CKF/RKF can be easily done if there are complex calculations involved in the reporting requirements.

“This saves your 70% development effort, instead of creating info objects for all the fields.”

But keep in mind, these fields you cannot get the benefits of NAV, existing SAP delivered iobj’s etc., as these are 3rd party external fields ( requirements) and basically don’t have any benefits of using the standard delivered content.

Check out the format option below – it should be Internal so that the data gets converted into the right format as per the ADSO field properties.

Regarding the date, the input date should be either on 01/01/2019 or 01.01.2019, based on the SU01 settings defined in the configuration. The date will be automatically converted to the DATS format  by using the RSDAT conversion routine in the extract structure. Check out the red window highlighted above.

If you have any other format, either it should be changed in the file or IOBJ transfer routine or use a different SAP delivered conversion routine. Press the selection option to check different conversion routines existing.

Field preview option

Try changing the Field Separator in the DS Extraction tab Data Separator as ,(Comma) and Escape sign as “ or ;

If the flat file value is 100.2222 but decimal place given as 3 then PSA gets the value as 100.222. If you have more decimal values in the flat file value try to increase decimal places itself in the flat file if you need it in PSA/ADSO.

NOTE:  All the decimal data got loaded in the PSA, same is visible in the ADSO and in SAP HANA cal view as well. Please note, the decimal sign might depend on the SU01 settings of the configuration of the user/system. Either it should show as . or , for the decimal and thousand sign. Please check this settings for your client systems. 

After the data got loaded, activated the request in the ADSO and accessed this ADSO table ends with DSO NAME2, in the sap cal view. Replicated all the excel calculations in SAP HANA , as its very easy to do it rather than in the SAP BW.

I created calculated columns in the SAP HANA Cal view, in the projection and few more in the Aggregation node to replicate all the excel manual formula calculations where the business was doing manually.

There is a high chance of mistakes if these were done manually in excel and its a huge work around and manual effort involved, where it can easily be automated through SAP BI batch process and replicating the same logic through SAP cal view easily.

All the Standard deviation, SQRT, Emission factors, Addition and division of values were easily implemented with the SAP standard functions in HANA cal views and the same emission factor values were done in SAP BI easily using the above process. Hope this helps for others who search and land in this page.

I thought of creating a document where it can be useful to anyone, as I had few challenges in getting the decimal values at the right precision in BI.

Last tip is uncheck the bloody SAP HANA execution check mark, where I somehow missed it and struggled a lot as the DTP failed all the time ….I was thinking its because of the field mapping somewhere I have done wrong….as it used to throw some weird index error or trex errors , field mapping errors…where I was searching in SDN to find a solution …and finally I saw this ….After unchecking the below, the DTP was successful.