Analytical Need
In many cases we need to use the metadata of the file name & the created date of a file as a filter/dimension in the dashboard.
For instance, use the created date as a snapshot date (when we union the files) and analyze the dashboard according to it.
Modeling Challenge
Currently we don't have such functions in the elasticube.
Solution
In the attachments section you will find an excel file that will do it for you using an excel macro.
The macro will add 2 new columns to the right of the last column - File Name & Created Date.
In order to achieve this you'll need to configure the following:
Make sure that the scripting option is enabled in excel:
So either:
a) Open excel and press alt + F11 (Visual Basic Editor)
b) Open excel, go to File-> Options -> Customize Ribbon and mark the developer Tab:
Once added pick the Visual Basic button:
Now go to Tools - > References. Scroll down and mark "Microsoft Scripting Runtime" , click OK and close the VBA editor screen.
Open the attached file (Make sure to enable macros in the file. Either as an "Enable Content" button below the formula bar or click on the "Macro Security" button on the developer tab). You will see only this button in it:
When you click the button it will open up the browse folders. Pick your file and click Open.
The macro will add the metadata to your file, will save it and close (you can re-open the file just to make sure).
Some mandatory assumptions:
1) The first row in the data file is the header row
2) The first column is always full until the last row (no nulls or missing values)
3) You can only do this one file at a time - not as a bulk
4) The macro only handles excel (xls/xlsx) & csv files
5) The date added is the create date of the file on the disk (as it appears in the file metadata)
6) Warning: If you open the changed csv file in excel, don't save it. It may get corrupted by Excel
7) if you do this twice to a file, you will have 2 sets of new columns
