Data Warehouse Consumers (DW)---a Technical Reference Guide for Designing Mission-Critical DW Solutions

  • Article

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

Consumers represent technology that allows end-users to access information derived from the data warehouse. An important distinction is that, typically the raw data are stored in the warehouse, but it is the consumers that transform the raw data into information that can actually be used by the business. Consumers provide an environment raw data may be transformed (decoded or enriched), and delivered in a form that is more easily analyzed (Business Intelligence [BI]). Typical data consumers include:

  • Tools/programs for extracting data from sources, transforming and loading (ETL)

  • Tools to execute queries and reports

  • Online Analytical Programs (OLAP) cube structures to quickly "slice and dice" data for analysis

  • A BI semantic layer to capture metadata (data definitions, and data lineage, for example)

  • Suites of interrelated applications and services

Case Studies and References

This section provides questions and issues to consider when working with your customers.

Questions and Considerations

This section provides questions and issues to consider when working with your customers.

  • Enforce Single version of truth6 for all data being consumed from any data layer in data warehouse.

  • Understand security requirements for data access:

    • Consider role-based security

    • What is feasibility of integration with Active Directory?

    • Are there integration point with custom security models

    • What are column and row based security requirements?

  • Understand Microsoft’s vision—see Business Intelligence to the Masses With SharePoint.7

  • Determine all connectivity requirements (e.g. connection managers, OLEDB, ODBC, JDBC, and so on).

  • Understand source database design (Star Schema, normalized).

  • For more effective management of data consumptions, consider classifying consumers as: Queries, Reports, BI Semantic Layer Tools, Embedded BI Applications, Suites of Applications and Services, and External Data Feeds .

  • Determine if the client will be using third-party BI products (e.g. MicroStrategy, Business Objects, Cognos) or will they use the Microsoft stack exclusively.

  • Understand the consumer deployment model(s) (e.g. will end-users simply run canned analysis/reports, or will they have direct access to the database?).

  • What is the refresh rate of consumer data (e.g. how frequently does it need to be updated)?

Appendix

Following are the full URLs for the hyperlinked text:

1 Top 10 Performance and Productivity Reasons to Use SQL Server 2008 for Your Business Intelligence Solutions http://sqlcat.com/top10lists/archive/2009/02/24/top-10-performance-and-productivity-reasons-to-use-sql-server-2008-for-your-business-intelligence-solutions.aspx

2 SQL Server Books Online msdn.microsoft.com/en-us/library/ms130214.aspx

3 What's new for PerformancePoint Services (SharePoint Server 2010) https://technet.microsoft.com/en-us/library/ee661741.aspx

4 PowerPivot https://www.powerpivot.com/

5 SQL Server2008 Reporting Services https://www.microsoft.com/sqlserver/2008/en/us/reporting.aspx

6 Single version of truth http://en.wikipedia.org/wiki/Single_version_of_the_truth

7 Business Intelligence to the Masses with SharePoint https://www.microsoft.com/presspass/features/2009/jan09/01-27kurtdelbeneqa.mspx