Data Warehouse and Business Intelligence Webinar October 23, 2014 - - PowerPoint PPT Presentation

Data Warehouse and Business Intelligence

Webinar October 23, 2014

Objectives

• What is the benefit of having a data warehouse?
• What kind of data can be stored in the PCI Data

Warehouse?

• How is Settlements data managed and accessed

with a data warehouse?

• What kind of BI reports are available to analyze

your data?

• How can you use Business Intelligence to validate

your charge codes?

Big Picture

DATA WAREHOUSE + BUSINESS DISCOVERY AND REPORTING = Transforming large amounts

• f transactional data into

actionable information

What is a Data Warehouse?

• A data storage system used for reporting and data

analysis.

– Integrates data from one or more sources creating a central repository of data. – Stores current and historical data used for creating dashboards and reports for senior management and key decision makers.

• The data stored in the warehouse is fed from transactional

systems.

– The data passes through an Operational Data Store (ODS) before it is used for reporting. – Reports and dashboards are fed from Data Marts which are highly focused for specific purposes.

• ODS and Data marts are Dimensional Data Models

Why Do I Need a Data Warehouse?

Top Reasons You Need to Use a DW instead of Transactional Database for BI: 1. Transactional data is not suited for reporting

– Often inconsistent and spread out – Table joins and format are designed for a different purpose and inefficient for reporting

2. BI and reporting tools require a well-defined structure 3. Reporting can slow a transactional system

Transactional vs. Warehouse

ODS vs. DM

Operational Data Store (ODS)

• The ODS organizes and stores all kinds of data

in a star schema. To name a few: – Energy Accounting – Settlements – Deals – Gentrader inputs and results – Bid Evaluator study results – Market Prices – Publicly Available Market data

Data Marts

• You may have multiple data marts, each

designed for a particular kind of report

ODS

Energy Acct DM Settlements DM P&L Analyzer DM

PCI Data Warehouse – Key Components

• Oracle and MS SQL Server both supported
• PCI Data Warehouse – Staging

– Interface to Non-PCI Source Systems – Designed for Standard ETLs

• PCI Data Warehouse – ODS

– Common Data Model – Optimized for Long-Term Storage – Data Stored in GMT – Star – Snowflake Schema

• PCI Data Warehouse – Data Marts

– Subsets of ODS – Optimized for Reporting – Settlement, Price, Meter, Deals, etc – Data Stored in Time Zone of Choice – Star – Snowflake Schema

What is a Star schema?

• The Data model used to create a data warehouse is

also known as a dimensional model.

– In this model, reference / lookup tables are called dimensions. – Measureable details of data are called measures or facts, and the tables that host them are called Fact Tables.

• In a Star Schema the Fact table sits in the middle and

is related to each dimension table by key dimensions.

PCI Data Warehouse Schemas

Operational Data Store (ODS) Data Mart (DM)

DM may be “generic” (one field for attribute and one for value) or “transposed” (pivoted)

DIM_COMMODITY COMMODITY_SK COMMODITY_NAME COMMODITY_GROUP AUDIT_SK DIM_EDITION EDITION_SK EDITION_NAME EDITION_APPROVED_IND DIM_QUALITY QUALITY_SK QUALITY_NAME DIM_POSITION POSITION_SK POSITION_NAME POSITION_TYPE COMPANY_NAME DIM_GROUPING GROUPING_SK GROUPING_LEVEL1 GROUPING_COLUMN1 GROUPING_LEVEL2 GROUPING_COLUMN2 GROUPING_LEVEL3 GROUPING_COLUMN3 GROUPING_LEVEL4 GROUPING_COLUMN4 GROUPING_LEVEL5 GROUPING_COLUMN5 GROUPING_SORT_ORDER_NUMBER DATE_NAME1 DATE_VALUE1 DATE_NAME2 DATE_VALUE2 DIM_FACT_ATTRIBUTE FACT_ATTRIBUTE_SK FACT_ATTRIBUTE_NAME FACT_ATTRIBUTE_CATEGORY FACT_ATTRIBUTE_SUBCAT FACT_ATTRIBUTE_SOURCE FACT_ATTRIBUTE_LONG_NAME FACT_ATTRIBUTE_DESC AMT_UOM QTY_UOM VALUE_UOM FACT_TABLE_NAME FACT_DATA_TYPE GRAIN_MINUTE_QTY AUDIT_SK FACT_4 OPERATING_TIME_SK POSITION_SK FACT_ATTRIBUTE_SK EDITION_SK GROUPING_SK COMMODITY_SK QUALITY_SK AUDIT_SK VALUE OUT_POSITION OUT_POSITION_SK POSITION_SK ATTRIBUTE_NAME ATTRIBUTE_VALUE DIM_AUDIT AUDIT_SK TASK_ID TASK_NAME START_DATE STOP_DATE ROWS_LOADED_QTY ROWS_FAILED_QTY DIM_TIME TIME_SK DATE_TIME YEAR MONTH DAY HOUR MINUTE

Data Models

Generic Fact Table Pivoted Fact Table

Data Warehouse Tools

• ODS Browser
• Data Mart Builder
• Data Export Utility (DEU)

ODS Browser

• Provides a view into the data available in the

ODS without requiring use of a separate SQL development tool.

• Useful with time compressed data

Data Mart Builder

• Creates a data mart from any data in the ODS
• You specify:
• Dimension tables and specific fields
• Fact data and table format (generic or pivoted)
• May include calculated values
• The wizard creates a “DDL”, or a SQL statement

that can be executed using a tool like SQL Developer to create the DM tables in your schema

Data Export Utility (DEU)

• DEU is a user-friendly reporting tool that allows you to

export specified front and back office data to an Excel template.

• Data is exported from the ODS so no performance

impact on the transactional Settlements systems

• DEU templates can be designed, shared and reused.

Business Intelligence Tools

Demo 3 common BI tools

• Access – Pixel-perfect reporting
• Excel – ad-hoc data analysis
• QlikView – dashboard for business

discovery and collaboration The PCI Data Warehouse is ready to use with YOUR in-house enterprise BI suite

BI Tools – MS Access

• MS Access is often overlooked as a BI tool
• Very good at “reporting”
• Intuitive query tool
• Not designed for business discovery
• Size limitations - link the tables to the data mart

BI Tools - Excel

• Most common BI tool
• Powerful analysis features
• Excellent for ad-hoc analysis
• Pivot tables
• Charts and graphs
• Mathematical formulas
• Size limitations
• Not for data storage

BI Tools - QlikView

• PCI BI platform tool
• Powerful analysis features
• Excellent for business discovery
• Drill groups and aggregations
• Visualizations
• Collaboration
• Data size limitations overcome with

hardware

P&L Analyzer - Settlements feedback

• Use PCI P&L Analyzer to

track:

• DA P&L
• DART P&L
• DART P&L Gains &

leakages

• Use P&L Metrics to provide

feedback loop to management on bidding strategies and potential market problems

• Get Market

Results

• Use P&L

Analyzer to gain insight

• Submit Bids
• Make changes

based on analysis

Strategize Engage Collect Analyze

PCI P&L Analyzer

PCI Settlements Analyzer

• Revolutionizes settlement workflow in the areas of data visualization,

settlement validation, and data reporting.

• Use settlement results to provide feedback loop to your front office …

and make on-course correction

• Monitor Settlements trends … and identify potential market problems
• Identify settlement discrepancies. Check whether these discrepancies

are routine or unique due to special operating conditions

• Supports settlements analytics

21

Strategize Engage Collect Analyze

Conclusion

• Industry research shows Data warehouse and BI

projects have a notoriously high failure rate

• PCI is successful because:
• We understands the data and tailor our

solutions and workflows to specific industry needs

• We cultivate a strong partnership with our

customers Questions? THANK YOU!