Proliance Reporting Strategies

Proliance Reporting Strategies ("Zee" Tan, Brant Carter, Meridian)

The data extraction features of Proliance are (from basic to complex)
1) Views and Filters: Views can be effective and meet basic needs. But they only allow a single data group per report (single table?), do not provide grouping or calculations, and limited formatting features are available.
2) Custom Print Layouts: More flexible, but can be hard to do include multiple data groups depending on document type.
3) Analytics (Cognos): Extremely flexible, allows charts and graphs. However, complex to understand. Uses Cognos hosted solution.
4) DBViews: Start of the data warehouse discussion...

Meridian is currently working on providing a data warehouse for POD users at no additional cost. (Q3 beta, Q4 release planned). With a data warehouse, they have seen report generation times drop several orders of magnitiude (hours have become seconds).

Three (SQL Server) databases in this approach:
Original database --(via replication)--> Mirrored database --(via ETL import)--> data warehouse

Proliance 4.01 is a prerequisite. The first part of the data warehouse tools being developed create the data warehouse tables from the mirrored database based on the DBViews. The Physical Data Model is transformed to something like the Logical Data Model. The ETL (Extract-Transform-Load) rules migrate the actual data. You report against the data warehouse, which is better suited for reporting. It includes constraints (PK, FK). Can generate an ERD from this so that the model is easy to understand. The second part creates files/information to support Cognos-specific implementations (Not applicable to us).

Size considerations: Original and mirrored database will be same size. Data warehouse typically 80% to 160% the size of the mirrored database. However, you don't need to back up the mirrored or data warehouse database.

Latency considerations: Need to select frequency of data warehouse update. Suggested that this be overnight. This is currently a drop and replace approach. A future vision is that this be deltas (only changes based on the last data warehouse update).

What was suggested for WESTON was that we download the data warehouse database down to our servers if we want to use our own reporting/BI tools.