The legacy Windows product is going away, and the vendor expects their customers to switch to the web version. To date, there is minimal conversion help and some DeskI features can not be replicated using Web Intelligence (aka WebI).
One of my clients decided instead to just eliminate Business Objects altogether and move to a different web-based BI product. To assist with the replacement initiative, we added a new feature to our BI Consolidator scanning engine. While the utility had always been able to look for keywords, for this BO analysis we needed to actually parse the SQL, extract table and column information into a database, and produce redundancy reports.
From the BO Repository, we copied the underlying SQL from the DeskI and WebI data providers for each BO Document (the reports). A utility parses the details and saves all of the data into an “inventory” repository for later analysis.
The following BO information is captured within the SQL inventory repository:
- BO Universe name and a calculated high-level “group”
- BO Folder name and group
- BO Userid
- BO Document name and group
- BO Data Provider name, type (DeskI or WebI), and group
- SQL Tables being accessed on FROM phrases
- SQL Table types (e.g., aggregate aware, summaries, history, hierarchies, security, etc.)
- SQL Table groups
- SQL Columns being accessed on SELECT phrases
- SQL Columns being referenced on WHERE phrases
- SQL Column types (e.g., count, amount, ID, code) and groups
These calculated “groups” allow us to organize information into higher level categories for redundancy analysis. With both category and detail data, we can discern which databases, tables, and columns are being used within thousands of BO reports. Tying this repository together with usage statistics, we can also determine which of the BO reports are of high usage and their associated data elements.