a complete bi solution in about an hour!
DESCRIPTION
In this presentation Aaron will cover how to collect data from multiple SQL Servers using SQL Server 2008 Integration Services (SSIS). Then he will use SQL Server Reporting Services (SSRS) to report detail on that data. After that he will use SQL Server Analysis Services (SSAS) to create a KPI. Finally he’ll present that KPI on a dashboard via a web page. The goal of this presentation is to show how seamless the Microsoft Business Intelligence products are. If you’ve only used a few of these products, you’ll appreciate seeing them together all at once. Code will be provided.TRANSCRIPT
A Complete BI Solution in about an Hour
Aaron Stanley King@trendoid
“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 is Business Intelligence?
Data storage Electronic Data Interchange (EDI) Online Analytical Processing (OLAP) Development support for predictive analytics Reporting Development support for integration Ad-hoc querying support
What do we need for a complete Business Intelligence solution?
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 as a dashboard
Agenda
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
Before we code, installation ;-(
Multidimensional 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 servername\multi and servername\tabular Multidimensional
MDX Large dataset Complex calculations, relationships and named sets Uses Facts and Dimensions
Tabular DAX Power View Large number of external data sources Uses Tables
http://technet.microsoft.com/en-us/library/hh231722(v=SQL.110).aspx
Dimension Tables Provide context Primary keys Smaller than fact tables
Fact Tables Used for measurements Numerics or integers Foreign keys Larger than dimension tables
Dimensions vs. Facts
Data Warehouse Design
http://msdn.microsoft.com/en-us/library/aa902672(v=SQL.80).aspx#sql_dwdesign_topic3
Data Warehouse Design
Demo
Aaron Stanley King http://www.aaronstanleyking.com @trendoid on Twitter
Questions and Answers