A Complete BI Solution in about an HourAaron Stanley King@trendoid

What is Business Intelligence?“Business Intelligence is a set of methodologies, processes, architectures, and technologies that transform raw data into meaningful and useful information used to enable more effective strategic, tactical, and operational insights and decision-making.“

-Boris Evelson, Topic Overview: Business Intelligence

What do we need for a complete Business Intelligence solution?• Data storage

• Extract Transform Load (ETL)

• Online Analytical Processing (OLAP)

• Development support for predictive analytics

• Reporting

• Development support for integration

• Ad-hoc querying support

Agenda• Some talk about SQL Server SQL Server (SSSS **)

• Collect data using SQL Server Integration Services (SSIS)

• Analyze the data using SQL Server Analysis Services (SSAS)

• Report on the analysis using SQL Server Reporting Services (SSRS)

• Display that information in a custom website

Merrill Aldrich #realProductNames

Before we code, installation ;-(• One installation for all services

• Development and management tools included

• One server install

• Low minimum hardware requirements Minimum: 1.4 GHz processor Minimum: 1 GB RAM Minimum: 5 GB Hard drive space free

*** subject to change and your exact environment

Multidimensional and Data Mining Mode or Tabular Mode• You can’t install both on one instance

• You will need to install two instances of SSAS That means run the setup twice

• Example: servername\multi and servername\tabular

• My environment has a multi-dimensional default instance servername\ and a tabular instance called servername\tabular

http://technet.microsoft.com/en-us/library/hh231722(v=SQL.110).aspx

Demo

Dimensions vs. Facts• Dimension Tables

Provide context Primary keys Smaller than fact tables

• Fact Tables Used for measurements Numeric or integers Foreign keys Larger than dimension tables

Data Warehouse Design

http://msdn.microsoft.com/en-us/library/aa902672(v=SQL.80).aspx#sql_dwdesign_topic3

Data Warehouse Design

Demo

Links and Resources• Downloads

Microsoft SQL Server 2012 Evaluation http://

www.microsoft.com/en-us/download/details.aspx?id=29066

Microsoft SQL Server 2012 SP1 Report Builder http://

www.microsoft.com/en-us/download/details.aspx?id=35576

SQL Server Data Tools – Business Intelligence for Visual Studio 2012 http://

www.microsoft.com/en-us/download/details.aspx?id=36843

SQL Server 2012 Feature Pack http://

www.microsoft.com/en-us/download/details.aspx?id=29065

• Tutorials and Videos Analysis Services Tutorials

http://msdn.microsoft.com/en-us/library/hh231701.aspx

Special Thanks• Microsoft Fargo

http://www.microsoft.com/ebc/fargo.mspx

• Confio http://www.confio.com

• PASS http://www.sqlpass.org

• Minnesota SQL Server User Group http://minnesota.sqlpass.org

• Nimble Storage http://www.nimblestorage.com

• Tegile http://www.tegile.com

Questions and Answers• Aaron Stanley King

http://www.aaronstanleyking.com @trendoid on Twitter