We are beginning a clean-sheet-of-paper architectural strategy for our overall reporting and BI needs. Part of this effort is the selection of front-end reporting tools to meet the full needs for both operational and analytical types of reports/analysis. We will be designing and building a historical data store and a data warehouse to serve these needs and will incorporate as much data as we need to in these systems. Are there any thoughts on processes and designs to quickly incorporate one-off spreadsheets into a server-based BI tool architecture? How is this type of issue generally solved in organizations?


Mike Jennings' Answer: I will defer commenting on the issues associated with integrating and reporting on data from desktop data sources outside of the data warehouse and provide a technical response. Many of the leading BI reporting tools allow for integration with desktop data sources in combination with other defined data stores (e.g., relational DBMS). The reporting product typically allows for meta data definition of the desktop data source locally (e.g., universe, catalog, package, etc.). The desktop data source meta data definition is then joined to the physical data store meta data definition for reporting purposes. The resulting report output file can then be published to the reporting product portal for distribution to other authorized users. One example of this is defining of desktop data sources through Cognos Impromptu hot files.

Les Barbusinski's Answer: The only two solutions that I can think of that meet your requirements (i.e., analytics derived from relational databases and desktop ODBC sources plus report publishing) are Microstrategy and Business Objects. Both have a "semantic layer" that a) shields the end user from the technical details of the data sources, and b) allows analytical reports to seamlessly combine information from a variety of data sources (e.g., relational, HTML, Excel, XML, ERP, etc.). Also, both tools permit "power users" to publish their reports to "public folders" that are accessible from the Web. The only caveat is that your power users will have to use the desktop version of the BI tool (rather than the Web-enabled version) to develop the multi-source reports.

The only problem is that in both cases, the data sources have to be predefined to the "semantic layer" ... and maintaining the "semantic layer" is not usually a function that is turned over to end users. As a result, your power users may have to coordinate their efforts with the BI administrator to import new spreadsheets into the semantic layer by placing them on a shared LAN drive and notifying the BI administrator of their existence. Hope this helps.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access