CSV Datasource Generator Excel Macro Template v2.0

[Changes on last version]

1. Hierarchy setting enablement

2. Renaming rule from zip to rar as the old zip file is not recognized by Windows Explorer (but working fine with WinRAR).

[Introduction]

With the release of 1.6 Design Studio, an outstanding feature – local csv datasource – has been introduced to support local demo purpose.

CSV datasource is not a common csv file. It’s a file set with at least two csv file – datasource.csv and datasource_metadata.csv. The usual way we get the CSV datasource is (1) add online datasource, (2) edit initial view, (3) Extract CSV Data

To simplify the process, I create a 1st version of data source generator template to simulate HANA datasource. With the assistance of this template, the first two steps could be saved.

Please bear in mind: This excel template is only an assistance with limited error-detection. Always remind the right data type is a good deed.

[Step by Step]

#1. Open template. This template is an excel with Macro with file suffix “.xlsm”. Two worksheets available – “Data Sheet” for dataset, “Dataset Metadata” for dataset metadata configuration. By default, some sample data contains in “Data Sheet” worksheet.

#2. Paste your own dataset into the area from line 4 under the “Data Set Area” title line (Like the sample starting line). The first line should always be the title of each column of the dataset. Only up to 100 columns/dimensions+measures are supported.

#3.  Click “Generate Metadata” button, select the whole dataset area (your dataset area), and then the titles will be added into “Dataset Metadata” worksheet corresponding area as dataset field. “HANA Dataset Metadata” worksheet will be activated. (Click pic to see animation)

#4. Configurate metadata for each field. “Role” and “Data Type” are foundamentally important. Any empty value for non-empty field will rise error when generating datasource.

• Role: Category of the field. “CHARACTERISTIC” – this field should be regarded as a characteristic or a dimension. “KEYFIGURE” – this field should be regarded as a key figure or a measure.
• Field Names: The name of the filed
• Data Type: Data type of a field. “DOUBLE” for float, double or decimal. “INTEGER” for integer. “STRING” for vchar, nvarchar… string-like data. “CALENDAR_DAY” for date.
• Description: The description of a field. Like label in HANA. Any charaters supported.
• With Unit: Assign a unit charateristic field name to a key figure field. e.g, if a characteristic field “UNIT” is a unit for price, at PRICE field, select “UNIT” at “With Unit” column.
• Aggregation Mode: For key figures, the Aggregation Mode should always be “SUM”

Sample:

#5. Global Settings is to set the global formatter function. From my test, “GROUP SEPARATOR” and “DECIMAL SEPARATOR” in design studio does not have any effect although I have changed “STANDARD TIMEZONE” to Europe/Berlin. So keep the default and everything will work.

#6. At “Navigation Path” column, if you want to enable hierarchy, put the dimensions/characterastics in expected order. Otherwise, leave this column blank. “Refresh” button is  a quick way to pick up all dimensions/characteristics and fill the column with them in data-defined order. Later can change the sequence.

Sample: Hierarchy definition: Date->Country->City

#7. Final step. Click “Generate DataSource”.

    -If “Navigation Path” is not empty, hierarchy function is enabled and first you should input the hierarchy name.

    -If “Navigation Path” is empty, datasource does not contain hierarchy.

    -Input the datasource name and select folder to store.

Finally, 2 files will be created together.

Then you can add this local CSV datasource to design studio and happy to use with it. (Click pic to see animation)

Sample: Data source in design studio with hierarchy enabled:

[Download Place]

This excel template is not so large, so I compressed it with a png and attached in this artical. Please follow the steps below to get it:

1. Save the picture below to your laptop

(<– Save me #1)

2. If you download the picture #1, change the file suffix from “png” to “rar”. (Ignore any warnings due to file type change).

If you download the picture #2, you may get a file named Datasource-Gernerator-v2.xlsm.zip. Delete .zip suffix to make it as “Datasource-Gernerator-v2.xlsm”. Now you  can open it directly and skip step 3.

3. Open and extract the rar to your laptop. xlsm excel template now is there!