Data Mashups/Dummy Data:
- In Lumira we have Data Prep Room.
- In Excel and Xcelsius we have Worksheet Ranges/Formulas
- In Webi we have Merge Dimensions
In Design Studio we have support for multiple data source support but no way to mash/merge the data up or create mock-up data like we can do with Excel/Xcelsius/Lumira Excel spreadsheet imports. I decided to enhance my datasource test component to do the following:
- Offer a spreadsheet-like interface to mock up data.
- Offer BIAL Methods to programatically add data in Script.
So here is what we now have:
Let’s look at the Property Sheets first:
The properties in the default property sheet are:
- Number of Dimensions – This indicates how many columns beginning on the left should be designated as Dimensions. The remaining columns will be treated as Measures.
- Measures in Rows (true/false) – This indicates whether Measures should be places on Rows or Columns Axis. The dimensions will occupy the opposite.
- Sort Method – Options are Alphanumeric Ascending or Descending, and None. Alphanumeric will sort by 1st Dimension, then 2nd, and so-on. None will just take order of occurrence.
If we then look at the Additional Properties Panels, we see a nice Excel-like view of our default data. I have included some helpful row and column menus illustrated below. As you see we can insert Dimension/Measure columns and rename headings. We can also insert/delete/update Rows.
I also threw in some Preset data when we do not care what the data is during mockup of a dashboard:
And also if you want a CSV view you can see you have that option in the ‘Options’ Menu. This is a more copy and paste-friendly option and then you can switch back at any time to the Table View:
So as we can see we have most of what we need to make dummy data. Mission accomplished here. What about mashing up data from multiple data sources? We’ll need to add some BIAL Methods:
- setHeaders(string) – Set your data source column headers by a comma-separated string (e.g. DS_1.setHeaders(“Department”,”Product”,”Sales”);
- setDimensionCount(integer) – Set how many columns should be considered dimensions.
- getDimensionCount() – Returns number of columns that are considered dimensions
- clear() – Delete all rows
- addRow(dimensions string, measures string, overwrite – true/false) – Add or overwrite a row with comma-separated dimensions then measures, and an optional overwrite flag (true/false). If you set to true, any existing row with the supplied dimension values is overwritten, otherwise it accumulates.
- deleteRow(dimensions string) – Deletes row(s) with specified comma-separated dimension string.
Let’s see an example in a BIAL snippet. I have 2 BW datasources and one BYOData datasource shown below.
Basically in each datasource I have 0CALMONTH. I then want to iterate over the 0CALMONTH values and grab a Measure from each datasource. There may be a more efficient way to do this, but for sake of simplicity (for me), this is what I came up with.
We also see that I am performing a calculation to generate a 3rd calculated measure (profit) on the fly. So even if you do not have 2 datasources, you could perform in-flight calculations and use the BYOData component as a data augmenter.
Let us see the end result:
And there we see the standard Design Studio charts component using our BYOData component at runtime with mashed up data from two different sources!
And with this BIAL scripting we can also do runtime perhaps ‘What-if’ scenario mocking up. Runtime before/after:
And here is the simple BIAL for the ‘Add’ Button:
I hope that this BYOData component can be found useful for folks. It will be updated into the SCN Community Repository shortly for anyone to use. Details here: SCN Design Studio SDK Development Community
Questions/Feedback/Comments always welcome!