Since the report script is containing all the filters for time dimension and other parameters - it is essential to keep it dynamic to avoid manual intervention. Lets save my report script content in a table T1 and instead of hard-coding the time value, I name it "DummyPeriod". Then using an ODI interface, my first step is to create the report script with the actual value of "DummyPeriod". Using the KM IKM SQL to File Append, I use the table T1 as my source and the file X.rep as my target. In the target, I use the REPLACE function to replace "DummyPeriod" with my actual period value (this value can get refreshed by a ODI variable). In the Flow properties for this IKM, the value for TRUNCATE must be set to True - else the Report Script will get appended with it's old content every time I create the report script using the ODI interface. Thus the report script gets created dynamically every time with dynamically generated values.
Now, in a new ODI interface, a datastore which matches the report script's number of output columns and data-types will act as the Source, and a database staging table will act as our Target. The LKM Hyperion Essbase DATA to SQL will be used here. It has the flow control parameters EXTRACTION_QUERY_TYPE and EXTRACTION_QUERY_FILE (refer page 9). Our values in this case will be ReportScript and X respectively. Within this interface, we can map the fields from the source to the target and use any filter or join or transformation as required.
Once the data is loaded in the target database staging table, we can implement standard ETL transformations to modify the data as per our required formatting. Then we can unload this data in a delimited file as required. Once the file is ready, it can be zipped and sent over as an email attachment to the intended recipients. It can also be sent via SFTP to any other server and path if required, thus completely eliminating any human intervention.
So now we can save our valuable time to catch some Pokemons! How do you automate your Smart View activities?