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 of 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) • Energy Acct The ODS organizes and stores all kinds of data DM in a star schema. To name a few: – Energy Accounting – Settlements Settlements ODS – Deals DM – Gentrader inputs and results – Bid Evaluator study results P&L – Market Prices Analyzer DM – Publicly Available Market data Data Marts • You may have multiple data marts, each designed for a particular kind of report

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) OUT_POSITION DIM_QUALITY OUT_POSITION_SK DIM_EDITION DIM_POSITION POSITION_SK QUALITY_SK EDITION_SK ATTRIBUTE_NAME QUALITY_NAME POSITION_SK EDITION_NAME ATTRIBUTE_VALUE POSITION_NAME EDITION_APPROVED_IND POSITION_TYPE COMPANY_NAME FACT_4 DIM_AUDIT OPERATING_TIME_SK DIM_COMMODITY POSITION_SK AUDIT_SK FACT_ATTRIBUTE_SK TASK_ID COMMODITY_SK EDITION_SK TASK_NAME COMMODITY_NAME GROUPING_SK COMMODITY_GROUP START_DATE COMMODITY_SK AUDIT_SK STOP_DATE QUALITY_SK ROWS_LOADED_QTY AUDIT_SK ROWS_FAILED_QTY VALUE DIM_GROUPING DIM_FACT_ATTRIBUTE GROUPING_SK FACT_ATTRIBUTE_SK GROUPING_LEVEL1 FACT_ATTRIBUTE_NAME DIM_TIME GROUPING_COLUMN1 FACT_ATTRIBUTE_CATEGORY GROUPING_LEVEL2 TIME_SK FACT_ATTRIBUTE_SUBCAT GROUPING_COLUMN2 DATE_TIME FACT_ATTRIBUTE_SOURCE GROUPING_LEVEL3 FACT_ATTRIBUTE_LONG_NAME YEAR GROUPING_COLUMN3 FACT_ATTRIBUTE_DESC MONTH GROUPING_LEVEL4 AMT_UOM DAY GROUPING_COLUMN4 QTY_UOM HOUR GROUPING_LEVEL5 MINUTE VALUE_UOM GROUPING_COLUMN5 FACT_TABLE_NAME GROUPING_SORT_ORDER_NUMBER FACT_DATA_TYPE DATE_NAME1 GRAIN_MINUTE_QTY DATE_VALUE1 AUDIT_SK DATE_NAME2 DATE_VALUE2 DM may be “generic” (one field for attribute and one for value) or “transposed” (pivoted)

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: o DA P&L o DART P&L o DART P&L Gains & leakages • Make changes • Submit Bids based on  Use P&L Metrics to provide analysis feedback loop to Strategize Engage management on bidding strategies and potential market problems Analyze Collect • Use P&L • Get Market Analyzer to Results gain insight

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 Strategize Engage Analyze Collect 21

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!