Commonly every one Build metadata and Data in Oracle Hyperion Planning EPMA application by using .ads format. In this Blog am going to say simple steps to design .ads file input format through Excel.
Data reorganize is nothing but load data from source to target cube including complex mappings. This is common exercise in Hyperion products (Planning, HFM) applications.
I used a database to hold all those mappings along with the source data. I would recommend this approach as you have full control on mapping what you are applying and you can keep track of what mapping changes you are doing since the data reorganize process is a repeatable process. After the mappings have been applied, I had to do some manual addition of few accounts subtract data from few accounts. Us Excel to the rescue. And the data is ready to be loaded to the database (cube).And change format (based on requirement) from “Column” to “Text data”.
Points to Remember
Maintaining Standard key
Always use a standard load rule and a standard data load format for the data file (sort the columns from least to most and the dense dimension at the last and for columns). Also, use a standard delimiter ( | @ ! < > ; )in the rule file to prevent forthcoming modifications.
Maintaining Individual Header
Have a individual header name to identify the dimension for the column. The dimension is identifiable if it has prefixes. Otherwise, always have a header record
Know your data first
While loading metadata system encounter a filed in the data source but sometimes won’t recognize either in Planning. This generally happens when you have a ‘Numbers’ in a ‘Member’ column whereas Planning accepts member name. There are instances where the member name is actually a number and when you use excel to do some transformation, excel converts the number to exponential format 1e+10. When you import the data to Excel / transform the data using “Text to Columns”, define the column data format as “Text” as shown below.
Special characters are not special
If you have special characters in your member names, ensure that the delimiter in the rule file is not one among those special characters. If you have spaces in your member names, ensure that you have quotes surrounded like “Opening Inventory” and your delimiter is not SPACE. If you are saving the file as a .csv and your member name has “,” then follow the same principle as above for SPACES.
Avoid Copy Paste
Do not copy the data from Excel to file and update the delimiter at every time.
All the above tips steps as a checkpoint to ensure that you have a repeatable process and following a standard methodology. Have a broader vision on how the process can be replicated for any forthcoming data reorganize processes. You have another option to do this through ODI / FMDEE but time consuming.
This Blog represent best way of design Metadata, convert .ads file (Not only .ads this way can use at any format) from “Text data” to “column” through Excel. And also it helps to get idea to build metadata in easiest way, it says how to avoid data copy past and instructions of selecting delimiters & Special characters.
Data Labs India is a digital transformation company helps organizations to achieve supply chain excellence through robust supply chain business processes and cutting edge technologies; empowered by AI and IoT. Our highly innovative solutions and products for end-to-end supply chain planning and operations make our clients truly competitive.