INTRODUCING THE CHICAGO SQL BI USER GROUP AND

INTRODUCING THE CHICAGO SQL BI USER GROUP AND BUSINESS INTELLIGENCE 101 January 17 th, 2012 The Business Intelligence User Group of Chicago Presented by: Emre Motan and Chi. SQLBI Board

Agenda Introducing the Chicago SQL BI User Group • Introduction to PASS • BI Professionals and Chicago • Introducing the Chicago SQL BI User Group • Topics of Interest for Members, Speakers, and Sponsors Business Intelligence 101 • What is Business Intelligence? • Microsoft BI vs. The Competition • Components in Microsoft’s BI Stack • Demo of SQL 2012’s Power. View application

Introduction to PASS • The Professional Association for SQL Server (PASS) sponsors chapters around the world. • Chicago’s local groups include one downtown and one in the western suburbs.

BI Professionals and Chicago • SQL Server BI used by many companies • SQL Server BI professionals in high demand by recruiters • Existing PASS SSUGs in Chicago focus on DBA topics • Existing BI groups in Chicago are multi-faceted • Business Intelligence User Group of Chicago (Meetup. com) • The Chicago Business Intelligence Group (Meetup. com) • Business Intelligence Roundtable (ITA) • Need for SQL Server BI-focused group

The Chicago SQL BI User Group • Opportunity for members to meet, learn, and network • Speakers, both experienced and novice, offered chance to present knowledge and build speaking portfolio • Sponsors sought to provide food, prizes, and swag in exchange for recognition • SQL Server User Groups collaborate for events such as SQL Saturday and the PASS Summit

Board of Directors • Marcello Benati • Director of BI, Rightpoint Consulting • Jung Choi • Data Architect, Sg 2 • Tom Huguelet • BI Architect, context. Q, Blue. Granite, Solid. Q • Tom Jaskula • Senior BI Consultant, MPS Partners • Jake Kremer • Solutions Architect, Project Leadership Associates • Emre Motan • Data Architect, Northwestern University Medical EDW

Topics of Interest (examples) • Data Warehousing SQL Server • Data modeling, dimensional analysis • Technical challenges and approaches • SSIS • Best practices, novel applications, data warehouse implementation • SSRS • Portal management, Share. Point integration, novel designs • SSAS • Cube development, optimization, management • Data mining principles and applications • SQL BI Enterprise Architecture

What is Business Intelligence? • Philosophically, it is the process of transforming data into information that enables valuable, actionable decisions • The business purpose of BI is to derive value from decisions enabled by analysis on business data typically from operational systems • In practice, BI refers to the management, transformation, storage, analysis, and presentation of information

How does BI work? SSIS & SQL Server SSAS SSRS Portal Share. Point Excel with Power. Pivot Power. View

How Microsoft Meets BI Needs • Microsoft offers SQL Server and its Business Intelligence stack • SQL Server holds data and provides T-SQL query engine • SSIS (Integration Services) provides tools to extract, transform, and load data (ETL) • SSAS (Analysis Services) provides tools to build multidimensional databases, develop data mining models, and query engine from cubes • SSRS (Reporting Services) provides tool to build reports and a portal to deploy reports so end users can run reports • Business Intelligence Development Studio (BIDS) is Visual Studio development environment for SQL BI

Major Players in BI Market • In 2010, Gartner put Microsoft in the “leaders” quadrant with 8. 7% market share along with Oracle, Microstrategy, IBM, and others.

Traditional BI vs. Self-Service BI • Microsoft intends to cater to both traditional and self- service BI customers with its varied offerings Traditional BI Information is delivered by: - Reports via subscription - Reports in SSRS portal - Reports in Share. Point - Static Dashboards Competitors: Oracle, IBM, SAP Self-Service BI Users develop information by: - Querying against cubes and data warehouse using Excel with Power. Pivot - Browsing and drilling down interactive dashboards using Power. View Competitors: Qlikview, Tableau

How do you purchase SQL Server? • Single Users • Demo licenses, SQL Server Express, and Developer edition are available for a nominal price ($50) • Boxed Software • Companies can purchase software and install on their own servers • BI Appliances • Custom-built hardware with software and customizations included can be bought from vendors such HP and their Business Decision Appliance • Cloud • Microsoft offers SQL Azure (SQL Server) and Reporting Services over their cloud network

SSIS – Integration Services • Tools to extract, transform, and load (ETL) data • Extract data from source databases or flat files • Transform data using SSIS components • Load data into destination tables or flat files • Can be used for a multitude of tasks, not just populating a database • File copying, moving, automation, etc. • Querying analytical applications • Parsing files such as XML and outputting contents • For BI, typically used to populate ODS and data marts • Can be used to merge data and populate master reference data based on inputs

SSAS – Analysis Services • Provides analytical capabilities on top of SQL Server • Allows creation of “cubes” which are multidimensional databases • Cubes can be considered as highly optimized pivot tables • Numerical data is held in “fact” table (e. g. inventory count, dollars) • Attributes are held in “dimension” tables (e. g. products, countries) • End users can query cubes using Excel or data can be fed to others applications like SSRS reports and data mining models • MDX language is used to query SSAS cubes; DMX language used to query Data Mining models built on SSAS cubes

SSRS – Reporting Services • Report building and presentation application • Users build reports in BIDS and deploy to SSRS portal • Portal manages security by role • Subscriptions can be programmed to deliver reports • Dashboards are a popular use of SSRS • Empowers management to get high level view of metrics and then dig deeper into specific areas via drill-down reports • Example: Northwestern uses custom built. NET portal on top of SSRS to management authorization in a securityheavy organization • E. G. Users can browse reports but have to request access to view contents

Solutions for MS BI Shortcomings • Metadata Management • Northwestern created a Metadata Browser that scrapes metadata from SQL system tables. • Packages that populate data warehouse tables (both ODS and DM) modify metadata repository with data such as last populated date • SSIS Package Management and Auditing • Version control is possible (packages are XML files) but cumbersome • Northwestern uses custom auditing solution for packages (ETL Execution Logs, metadata columns to each destination like execution GUID and datetime) • SSIS frameworks exist, such as in book SSIS 2008 Problem, Design, and Solution • Managing jobs and schedules • SQL Server Agent should be used to schedule jobs and track history • Third-Party products such as SQL Sentry are useful for auditing and management

Demo – Power. View