DATA WAREHOUSING IN SQL SERVER 2005/2008 BUSINESS INTELLIGENCE

levelsordΔιαχείριση Δεδομένων

20 Νοε 2013 (πριν από 3 χρόνια και 6 μήνες)

72 εμφανίσεις

DATA WAREHOUSING IN
SQL SERVER 2005/2008

BUSINESS INTELLIGENCE


E
XPERIENCE

WITH

BI


Developing end
-
to
-
end BI prototype for Plan
International


Extensive project experience with Reporting
Services


Books, courses, exams and played


Developed numerous reporting apps that should
have been BI solutions!

C
ONCEPTS


Business Intelligence


Organisation
-
wide perspective


Combining data from diverse systems


High level aggregation to detailed drill
-
down


Relational data not designed for BI


Narrow focus on specific business requirement


OLTP for speed of entry and integrity


OLAP (On
-
line Analytical Processing) for
aggregating large data volumes



C
ORE

P
ROCESSES


ELT (Extract, Load, Transform)


Import, clean and combine diverse data


Store in Data Warehouse


Star Schema


Relational structure for OLAP, not OLTP


Cubes


Pre
-
aggregate results (sum, count)


Reporting


(Data Mining and Prediction)


SQL S
ERVER

2005


SQL Server Management Studio


Design and query databases


Replaces Enterprise Manager, Query Analyser


Manage Servers (DB, AS, RS, IS)


SQL Server Business Intelligence Studio


VS .NET 2005 projects


Design, build and deploy


B
USINESS

I
NTELLIGENCE

S
TUDIO


Integration Services


Data import and transform (replaces DTS)


Merge data into Star Schema


Analysis Services


Design and populate Cubes


Report Model


ERD for reporting


Report Server


Design and publish reports



S
TAR

S
CHEMA


Star focuses on business area


Sales, Logistics


Fact Table stores numeric measures


Order Value, Order Quantity


Dimension Tables


How data is analysed (aggregates and heirarchies)


Product, Customer, Time



DATA WAREHOUSE = BUSINESS
INTELLIGENCE


Business Intelligence (BI) refers to skills,
processes, technologies, applications and
practices used to support decision making.


historical, current, and predictive views


reporting, OLAP, analytics, data mining, business
performance management, benchmarking, text
mining, and predictive analytics.


A data warehouse is a repository of an
organization's electronically stored data. Data
warehouses are designed to facilitate reporting
and analysis.


business intelligence tools, tools to extract, transform,
and load data into the repository, and tools to
manage and retrieve metadata.

BI IN SQL SERVER 2005

Component

SQL Server 2005

Extract, transformation, and load (ETL)

SQL

Server

2005 Integration Services

Relational data warehouse

SQL

Server

2005 relational database

Multidimensional database

SQL

Server

2005 Analysis Services (SSAS)

Data mining

SQL

Server

2005 Analysis Services

Managed reporting

SQL

Server

2005 Reporting Services

Ad hoc reporting

SQL

Server

2005 Reporting Services

Ad hoc query and analysis

Microsoft Office products (Excel, Office Web
Components, Data Analyzer, SharePoint Portal)

Database development tools

SQL

Server

2005 Business Intelligence Development
Studio

Database management tools

SQL

Server

2005 SQL

Server Management Studio

BASIC CONCEPTS


Universal Dimensional Model


Cube


Drill
-
down


Roll
-
up


Online Analytical Processing (OLAP)





To create a new Analysis Services project, follow these
steps:


Select Microsoft SQL Server 2005 > SQL Server Business Intelligence
Development Studio from the Programs menu to launch Business Intelligence
Development Studio.





To define a
Data source

for the new cube, follow these steps:


Right
-
click on the Data Sources folder in Solution Explorer and select New
Data Source.




To create a new data source view, follow these steps:


Right
-
click on the Data Source Views folder in Solution Explorer and select
New Data Source View.



BIDS will automatically display the schema of the new
data source view


To create the new cube, follow these steps:


Right
-
click on the Cubes folder in Solution Explorer and select New Cube.





D
EPLOYING

,P
ROCESSING
, B
ROWSING

A

C
UBE


T
ERMINOLOGIES


Cube

The basic unit of storage and analysis in Analysis Services is the
cube
. A
cube is a collection of data that’s been aggregated to allow queries to
return data quickly.



Dimension

Each cube has one or more
dimensions
, each based on one or more
dimension tables. A dimension represents a category for analyzing
business data



Fact table

A
fact table

contains the basic information that you wish to summarize.
This might be order detail information, payroll records, or anything
else that’s amenable to summing and averaging.

WHERE TO GET SQL SERVER


UNCC MSDNAA
:


http://msdn04.e
-
academy.com/elms/Storefront/Home.aspx?campus=un
cc_cit


SQL Server 2005 Standard Edition


SQL Server 2008 Developer Edition


SQL Server 2008 Enterprise Edition