Skip to Content

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”

ex1-.PNG

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 Terry Yang)

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?

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Debajyoti Dan

    Hi Karol,

          Thanks for sharing such a nice blog. I have a query on this CSV source file feature which I wanted to clarify. Is there any way to use any offline CSV file as a data source using this functionality ? Instead of downloading existing datasource as csv and then use it later offline.

    Thanks & Regards,

              Dan

    (0) 
    1. Karol Kalisz Post author

      Hi Dan,

      yes, there is. Only what you need to do is to add the column descriptions and the corresponding meta data. I have found a blog from Terry, and have updated my blog with the link at the end.

      Karol

      (0) 
  2. Tom Francois

    Hi Karol,

    Thanks for this great blog.

    I’m just starting with exploring DS1.6 and for  a POC we want to buil in DS, we want to use CSV data (as setting up the BW datamodel will take too much effort for a POC), so i’ll have the create the metadata CSV myself.

    Looks pretty straightforward with above explanation.

    What i am missing though is how to upload this metadata-csv to the application in Design Studio? I can see how to upload the data CSV, but not the metadata CSV. Am i missing something obvious?

    (0) 
    1. Karol Kalisz Post author

      Hi

      you basically need to place both files with the given naming conventions in the same folder  and then select the csv file in design studio. Ds will pick up both files together. Have you tried it This way?

      Karol

      (0) 
  3. Gerhard Guggelberger

    Hi Karol,
    i have one question – can i use a csv-data source when i am running on bi-platform and not locally – i have uploaded a csv file in the cms but when i want to choose the file in the ds application i only can find pictures… when i write the filename the system can not find it…

    is this possible on bi-platform???
    br
    Gerhard

    (0) 

Leave a Reply