1 / 18

Introducing the Chicago SQL BI User Group and Business Intelligence 101

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 ChiSQLBI Board. Agenda. Introducing the Chicago SQL BI User Group Introduction to PASS BI Professionals and Chicago

theo
Download Presentation

Introducing the Chicago SQL BI User Group and Business Intelligence 101

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introducing the Chicago SQL BI User GroupandBusiness Intelligence 101 January 17th, 2012 The Business Intelligence User Group of Chicago Presented by: Emre Motan and ChiSQLBI Board

  2. 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 PowerView application

  3. 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.

  4. 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

  5. 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

  6. Board of Directors • Marcello Benati • Director of BI, Rightpoint Consulting • Jung Choi • Data Architect, Sg2 • Tom Huguelet • BI Architect, contextQ, BlueGranite, SolidQ • Tom Jaskula • Senior BI Consultant, MPS Partners • Jake Kremer • Solutions Architect, Project Leadership Associates • Emre Motan • Data Architect, Northwestern University Medical EDW

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

  8. 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

  9. How does BI work? SSIS & SQL Server SSAS SSRS SSRSPortal SharePointExcel with PowerPivotPowerView

  10. 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

  11. 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.

  12. Traditional BI vs. Self-Service BI • Microsoft intends to cater to both traditional and self-service BI customers with its varied offerings

  13. 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

  14. 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

  15. 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

  16. 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 security-heavy organization • E.G. Users can browse reports but have to request access to view contents

  17. 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

  18. Demo – PowerView

More Related