| Area | Option No | Option | Description | Development Efforts | ETL Requirement | Developer Complexity | User Complexity | Performance | Security | Maintenance Efforts | Slowly Changing Dimension Support |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Data Modeling | |||||||||||
| 1 | Direct SQL in Report Studio | No data modeling required. Use SQL statement to extract data at run time. Dynamic SQL will be created when prompt parameter is embedded in SQL. | 2-4 hrs | None | Low, assuming developer knows ODS well | High | Low, as it impact the ODS | Low. User can specify their own SQL. UCI requires Restricted Developer KSAMS role | High. Request ODS knowledge. | No | |
| 2 | Simple FM model on ODS model | FM model is developed with SQL statements as data source. | 8-40 hrs | None | Low, assuming developer knows ODS well | Low. FM package expose business layer info. | Low, as it impact the ODS | High. Secured by KSAMS | Low | No | |
| 3 | Simple FM model on ODS model using view | FM model is developed with SQL view as data source. | 8-40 hrs | None | Low, assuming developer knows ODS well | Low. FM package expose business layer info. | Low, as it impact the ODS | High. Secured by KSAMS | Low | No | |
| 4 | Simple FM model on ODS model using materialized/indexed view | FM model is developed with Materialized/Indexed view as data source. | 16-40 hrs | None | Low, assuming developer knows ODS well | Low. FM package expose business layer info. | High, if all materialized/indexed views are extensively indexed | High. Secured by KSAMS | Medium. Materialized/indexed views refreshment requires a bit of coding/scheduling. | No | |
| 5 | Simple FM model on ODS model using a cloned ODS | FM model is developed with cloned ODS data source. | 16-40 hrs | None | Low, assuming developer knows ODS well | Low. FM package expose business layer info. | High. No impact on production ODS | High. Secured by KSAMS | Medium. Replication requires a bit of coding/scheduling. | No | |
| 6 | Simple FM model on ODS model using simple dimensional model (e.g. UCI Cognos Performance) | Dimensional DB schema. Relational Cognos model. | 40-80 | Yes | Medium. DB design required. | Low. FM package expose business layer info. | High. No impact on production ODS | High. Secured by KSAMS | Medium. ETL, DB development required. | Yes | |
| 7 | Simple FM DMR model on ODS model using complex dimensional model (e.g. UCI Cognos Performance) | Dimensional DB schema. Relational Cognos model. | 60-100 | Yes | Medium. DB design required. | Low. FM package expose business layer info. | High. No impact on production ODS | High. Secured by KSAMS | Medium. ETL, DB development required. | Yes | |
| 8 | Moderate FM model on ODS model using simple dimensional model (F5 Performance Monitoring, KSAMS) | Dimensional DB schema. Relational Cognos model. | 100-160 | Yes | Medium. DB design required. | Low. FM package expose business layer info. | High. No impact on production ODS | High. Secured by KSAMS | Medium. ETL, DB development required. | Yes | |
| 9 | Moderate FM DMR model on ODS model using complex dimensional model (F5 Performance Monitoring, KSAMS) | Dimensional DB schema. Relational Cognos model. | 100-160 | Yes | Medium. DB design required. | Low. FM package expose business layer info. | High. No impact on production ODS | High. Secured by KSAMS | Medium. ETL, DB development required. | Yes | |
| 8 | Complex FM model on ODS model using simple dimensional model (KFS, UCPath) | Dimensional DB schema. Relational Cognos model. | Months | Yes | Medium. DB design required. | Low. FM package expose business layer info. | High. No impact on production ODS | High. Secured by KSAMS | High. ETL, DB development required. | Yes | |
| 9 | Complex FM DMR model on ODS model using complex dimensional model (KFS, UCPath) | Dimensional DB schema. Relational Cognos model. | Months | Yes | Medium. DB design required. | Low. FM package expose business layer info. | High. No impact on production ODS | High. Secured by KSAMS | High. ETL, DB development required. | Yes | |
| Report Creation | |||||||||||
| 1 | Cognos Tool: Workspace Advanced | Self-serve BI and reporting | 16-40 | See data modeling | Low. | Low, if package properly vested, designed and implemented | Same as others | High. Integrated with KSAMS | Low. User maintains their own. Cognos developer maintains package. | See data modeling | |
| 2 | Cognos Tool: CAFÉ | Self-serve BI and reporting | 16-40 | See data modeling | Low. | Low, if package properly vested, designed and implemented, and users are proficient with Excel | Same as others | High. Integrated with KSAMS | Low. User maintains their own. Cognos developer maintains package. | See data modeling | |
| 3 | Report Studio: Canned report. Simple with no prompts, few summary | Developed by Cognos developers, execute by users. | 2-4 hours | See data modeling | Low. | Low | Same as others | High. Integrated with KSAMS | Low | See data modeling | |
| 4 | Report Studio: Canned report. Medium complexity with more than 10 prompts, groupings and charts | Developed by Cognos developers, execute by users. | 20-40 hrs | See data modeling | Low. | Low | Same as others | High. Integrated with KSAMS | Low | See data modeling | |
| 5 | Report Studio: Canned report. High complexity with more than 10 prompts, groupings and charts, dynamic columns and sort selection (GL Transaction and Summary) | Developed by Cognos developers, execute by users. | 40-160 hrs | See data modeling | Low. | Low | Same as others | High. Integrated with KSAMS | Low | See data modeling | |
| 6 | Cognos Application (GL Adhocs) | Cognos Workspace Advanced replacement | Weeks-Months, see estimate worksheet below | See data modeling | Low. | Low | Same as others | High. Integrated with KSAMS | Low | See data modeling |
Estimate Worksheet
| Data & Modeling | No | Item | Description | Count | Unit Hrs | Total Hrs |
|---|---|---|---|---|---|---|
| 1 | Selected Data Modeling Option | Select data modeling base upon needs | ||||
| 2 | Model design | Design Relational/Dimensional per #1 | ||||
| 3 | DB Design | Implement DB per #1 | ||||
| 4 | ETL | ETL per #1 | ||||
| 5 | Security | Report/Object/Row level security design and implementation | ||||
| 7 | Framework | Framework Design & Development | ||||
| 8 | Package | Package Design & Development | ||||
| Report Development | ||||||
| 1 | Filter criteria | Count | Unit Hrs | Total Hrs | ||
| How many are text | ||||||
| How many are check boxes | ||||||
| How many are dates | ||||||
| How many are drop down | ||||||
| How many are search & select | ||||||
| 2 | Output options | |||||
| Default columns: what are they | ||||||
| Selectable columns? Yes-> how many | ||||||
| Sort-table columns? Yes-> how many | ||||||
| 3 | Drill down? | |||||
| How many? To existing or new reports? | ||||||
| 4 | Dynamic Grouping: | |||||
| How many levels? | ||||||
| How many different columns? | ||||||
| 5 | Dynamic Sub-Total | |||||
| How many different columns? | ||||||
| 6 | Save query? | |||||
| Project Management | Count | Unit Hrs | Total Hrs | |||
| Requirement Spec | ||||||
| Functional Spec | ||||||
| Test Specs | ||||||
| SN Project | ||||||
| UAT | ||||||
| Meeting/Project Updates | ||||||