Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
Karol-K
Advisor
Advisor

as there was already a discussion on this topic -SAP Design Studio 1.6 local mode CSV datasource not working - here some insides on the CSV data source in Design Studio 1.6.

What is this for?

The first question is - why this CSV data source and what is this for? As you can take from the documentation, the csv data source is available in the local mode and cannot be used when working on any platform. This is already "restricting" the usage of this feature. It is targeted for offline demonstrations, work with sample data and show cases of applications for POCs.


When using for some CSV based local reports, it is working good for this as well. You need to consider only that because of the "demo" scope, the performance of bigger data sets can suffer.


The difference to SDK and Community CSV data sources

There are two interesting difference to the SDK data sources which can provide CSV access as well.


The first one is, this CSV is fully underneath of BICS data access layer - it means also components which can be assigned only to real data sources can use it. By this, the full (basic) function scope can be used - including filter components and crosstab visualization.

The second one is, the data source contains not only the data, but also metadata and possible hierarchies for the data. This is helpful for use in the components (eg what is dimension and what is measure) and allows more functions than the SDK CSV data sources. With this we can come to the structure topic, as this is where many fail...

The structure

The CSV data source consists of 2 files, the data (*.csv) and the metadata (*_metadata.csv). Without the second file it will not recognize the CSV file as CSV data source. In addition, you can find also some state file (*.xml) which describes the changes made in the initial view editor on top of this data source.

The content of *.csv file

On the first view it is a normal semicolon separated file.

here are the first 2 lines of the example used in blog /community/businessobjects-design-studio/blog/2015/11/23/design-studio-16--view-on-scorecard-component


YEAR_QUARTER;CUSTOMER;CUSTOMER;LONGITUDE;LATTITUDE;CITY;PRODUCT_GROUP;PRODUCT_GROUP;DISTR_CHANNEL;DISTR_CHANNEL;AREA_CODE;AREA_CODE;REGION;REGION;BILLED_QUANTITY;BILLED_QUANTITY;SALES_VALUE;SALES_VALUE;PRICE;PRICE



201403;DS1;Sandstone  Ltd;-91.2547266;30.36812929;BATON ROUGE;DS10;Bag & Outdoor;0;Internet;8050322;Louisiana;80503;South East;1372;PC;15219;USD;11.09;USD




On second view, you can see that this structure has more content then you can expect - like duplicate columns for the same dimension, with text, key and attributes.

The first line is basically a description - but also here it is the "technical name" of the column. This is the main link to the corresponding metadata file.

The second and next lines are simple data which belongs to the result set.

Specialty of the content

When you check the first line, you will see duplicates..


201403;DS1;Sandstone  Ltd;-91.2547266;30.36812929;BATON ROUGE;DS10;Bag & Outdoor;0;Internet;8050322;Louisiana;80503;South East;1372;PC;15219;USD;11.09;USD


201403;DS1;Sandstone  Ltd;-91.2547266;30.36812929;BATON ROUGE;DS20;Accessories;0;Internet;8050322;Louisiana;80503;South East;8940;PC;21110;USD;2.36;USD


201403;DS1;Sandstone  Ltd;-91.2547266;30.36812929;BATON ROUGE;DS30;Office;0;Internet;8050322;Louisiana;80503;South East;7770;PC;16201;USD;2.09;USD




and this is because the CSV file is containing the data and also the masterdata in the same file. E.g. the customer "DS1" is having text "Sandstone  Ltd" and all 3 lines are having the same value. From this perspective it is probably not the most optimal format for CSV. The good news is, you can skip the columns if you do not distinguish on key text and do not need any atributes.

The content of *metadata.csv file

Now, let's look into the metadata file. Here is the full content:

/rows are numbered by me for explaination/


01 <<BEGIN OF METADATA>>;;;;;;


02 <<BEGIN OF ROLESUPPORT>>;;;;;;


03 Role;Name;Description;Field;Referenced Characteristic;Presentation;Data Types


04 CHARACTERISTIC;YEAR_QUARTER;Year Quarter;1;;KEY;STRING


05 CHARACTERISTIC;CUSTOMER;Customer;2;;KEY;STRING


06 CHARACTERISTIC;CUSTOMER;Customer;3;;TEXT;STRING


07 ATTRIBUTE;LONGITUDE;LONGITUDE;4;CUSTOMER;KEY;STRING


08 ATTRIBUTE;LATTITUDE;LATTITUDE;5;CUSTOMER;KEY;STRING


09 ATTRIBUTE;CITY;City;6;CUSTOMER;KEY;STRING


10 CHARACTERISTIC;PRODUCT_GROUP;Product Group;7;;KEY;STRING


11 CHARACTERISTIC;PRODUCT_GROUP;Product Group;8;;TEXT;STRING


12 CHARACTERISTIC;DISTR_CHANNEL;Distribution Channel;9;;KEY;STRING


13 CHARACTERISTIC;DISTR_CHANNEL;Distribution Channel;10;;TEXT;STRING


14 CHARACTERISTIC;AREA_CODE;Area Code;11;;KEY;STRING


15 CHARACTERISTIC;AREA_CODE;Area Code;12;;TEXT;STRING


16 CHARACTERISTIC;REGION;Region;13;;KEY;STRING


17 CHARACTERISTIC;REGION;Region;14;;TEXT;STRING


18 KEYFIGURE;BILLED_QUANTITY;Billed Quantity;15;;VALUE;DOUBLE


19 KEYFIGURE;BILLED_QUANTITY;Billed Quantity;16;;UNIT;STRING


20 KEYFIGURE;SALES_VALUE;Sales Value;17;;VALUE;DOUBLE


21 KEYFIGURE;SALES_VALUE;Sales Value;18;;CURRENCY;STRING


22 <<END OF ROLESUPPORT>>;;;;;;


23 <<BEGIN OF FORMAT SETTINGS>>;;;;;;


24 TYPE;VALUE;;;;;


25 GROUP SEPERATOR;,;;;;;


26 DECIMAL SEPERATOR;.;;;;;


27 DATE SEPERATOR;/;;;;;


28 STANDARD CURRENCY;$;;;;;


29 STANDARD TIMEZONE;GMT;;;;;


30 <<END OF FORMAT SETTINGS>>;;;;;;


31 <<BEGIN OF HIERARCHIES>>;;;;;;


32 Type;Name;Reference Characteritic;Level Count;;;


33 LEVEL;DistributionChannel;PRODUCT_GROUP;1;DISTR_CHANNEL;;


34 LEVEL;ProductGroupHier;DISTR_CHANNEL;1;PRODUCT_GROUP;;


35 LEVEL;Region;AREA_CODE;1;REGION;;


36 <<END OF HIERARCHIES>>;;;;;;


37 <<END OF METADATA>>;;;;;;




How to understand this and how to recreate for really custom CSV content.

Rows 03 to 21 are the most important for simple content.

Row 03 is description

Row 04 is introducing a characteristic (dimension) "YEAR_QUARTER" and it is containing the KEY. This dimension is very simple (only having the key)

Row 05 is introducing a dimension "CUSTOMER" and this is more complex.

Row 06 is adding the "TEXT"

Row 07 - 09 are introducing attributes linked to the dimension "CUSTOMER"

Rows 10 - 17 are similar for other dimensions

Row 18 is introducing a keyfigure (measure). Those will be "cummulated" in the Measure DImension

Rows 24 - 29 are containing some metadata to the metadata 😉

Rows 32 - 35 are containing hierarchies (in this case some basic one)

In general you can try to define your  own CSV file and create corresponding metadata for it. As you see, the metadata file is not too complex and removing many lines you do not need you can simulate it of any custom CSV content. Also, the naming here is a kind of "BW-based", you can see characteristic and keyfigures instead of dimensions and measures - but this is how BICS work internally, so no not confuse yourself.

How to create such CSV files?

The simplest way is to add standard data source from any system into Design Studio and then do following:

1. open "initial view editor" (menu -> "Edit Initial View")

2. press "Extract CSV Data"

and the files will be created.

Now, you can use the new files to add new CSV data source. You can chose such files from anywhere, those will be always copied to your application folder.

The file *.xml

If you have edited something in the initial view editor on this data source, you will get additional file *.xml but this one cannot be created by yourself for custom CSV, so it is not so important. This file is basically including all information about changes in the data source  (filters, drill down, settings for results etc). It means, if you do not want to start always from scratch, first edit the data source, save and then export to csv.

CSV-Format File Generator (by terryyang)

In the meantime, my colleage Terry has posted a blog on an excel generator for the CSV files (format as required by Design Studio). You can take a look.

CSV Datasource Generator Excel Macro Template v2.0

Any more questions?

6 Comments