SQL 2005 DataWarehouse Note

@2007 June 02

MS BI Studio

fact table (transaction table)
dim table (master file: product, customer)

BI Studio
1)Datasource: Delphi ADOConnection
2)Datasource View: ADOTable
3)Cube
4)Dimension

mainly involve: data source view, cube, dimension

Cube
N measures (sale, qty) from a fact table
N dimensions (produt, time)

=========================================
Task: Add a new cube without a datasource
=========================================
1)select AdvWorks standard edition
2)choose measures:
clear all
select internal sales amount, qty
3)choose dimension
4)define time period: year, quarter, month, Data is mandatory key column
5)add special calendar
6)relate dimension to measure
7)name and complete
click measure panel, select the target cube, set default measure to a valid one: internal sales amount
8)GENERATE a database table base on the cube
9)load data into the newly generated table
10)deploy. (the Analysis Service must be started)
Cube deploy located in <SQL Dir>/MSSQL.x/OLAP/Data/<Cube Name>
Similar to EJB container
11)complete and cube is ready for browse


OBJECT RELATIONS
DB engine: provide underlying data fact tables, dimension tables
Analysis Service: Cube container(similar EJB container)
BI Development Studio: Development tools(VS.2005), develop a cube and deploy it into Analysis Service directory.
SQL Mgmt Stduio: connect to Analysis Service Server and query the Cube with MDX