Northwind Lite Data Warehouse Plan - Assignment #3

Data Warehouse Plan

Peter Blaney

BI Certification

Solution: NorthwindLite Data Warehouse

Date: 10/27/2014

Blog: http://blaneybi.blogspot.com/

The Northwind Lite database, being a very small data collection, is already largely appropriately structured for a data warehouse. The below table shows the comprehensive data warehouse object definitions.

Data Warehouse Objects Definitions

The only structural modification for the existing data set made between the transactional database and the data warehouse was in integrating the category dimensions into the product table. This was done because the category domain was small and easily integrated under the product table for more efficient analytical processes.

Products Dimension table compiled from the Products and Category Tables

Once the data warehouse structure was developed for the existing data, surrogate keys were added for each table, the date/time stamps were broken up where appropriate, and a date dimension was added to provide analysis functionality by time period.

Date/Time Stamp added

Date Dimension

Finally, for the customers, a state dimension was added for regional analysis. Also, a date dimension and current tag was added for when customer contact information changes.

Customer Modifications

Blaney BI Solution - Assignment #2

Solution Development Plan

Solution: Personal Finance Tracker

Owner: Peter Blaney

Date: 10/20/2014

Change Log

Who	When	What

Summary

Personal households would benefit from a comprehensive analysis of their expenses. Utilizing all bank accounts as a foundation, transactions should be compiled into an integrated financial analysis tool.  The tool should allow for detailed assessments of various expenses for trending and identifying anomalous behavior, as well as tracking funds to ensure money is received appropriately and allocated according to need.

Expectations

1.      The solution will store and present verified data.

2.      The solution will allow for simple ad hoc queries.

3.      The solution must include a data warehouse that is easy to use.

4.      The solution should include an OLAP cube.

5.      The solution should be simple to keep development and maintenance costs at a minimum.

6.      A working prototype should be available in a short period of time.

Open Issues

Who	When	What

Closed Issues

Who	When	What

Solution Naming Conventions

ETL Staging Database (SQL Server)

Object Type	Convention	Example	Description
Table	EtlTableName	EtlTimeTable	A table used to temporarily hold data for the ETL process
View	vEtlViewName	vEtlCastAuthorsData	A saved select statement that provides ETL data
Stored Proc.	pEtlProcedureName	pEtlTransformAuthorNames	A set of saved statements that provides ETL data
Function	fEtlFunctionName	fEtlLookupValues	A saved select statement that provides ETL data
…

Data Warehouse Database (SQL Server)

Object Type	Convention	Example	Description
Fact Table	tblTableName	tblTransactions	A table that holds measures and dimensional keys
Dimension Table	txTableName	txParties	A table that holds dimensional keys and descriptive dimensional attributes
View	vRptViewName	vRptSalesByTitle	A saved select statement used for reporting
Stored Proc.	pRptProcedureName	pRptSalesByTitle	A saved set of statements used for reporting
Function	fRptFunctionName	fRptSalesByTitle	A saved select statement used for reporting
…

ETL Processing Objects (Integration Services)

Object Type	Convention	Example	Description
File Connection	NameOfFile	ZipCodeList.txt	A SSIS File connection object
Database Connection	ServerName.DatabaseName	LocalHost.Pubs	A SSIS DB connection object
Task	ObjectTypeObjectName	DataFlowLoadDimTitles	A SSIS task object
Container	ObjectTypeObjectName	SequenceContainerLoadFactTables	A SSIS container object
…

OLAP Cube Database (Analysis Server)

Object Type	Convention		Description
Data Source	NameOfDataWarehouse	DWPubs	A SSAS Data Source
D.S. View	NameOfConnectionNameOfSubject	DWPubsSalesFacts	A SSAS Data Source View
Dimension	DimNameOfSubject	DimTitles	A SSAS Dimension
Cube	NameOfSubject	CubeSalesFacts	A SSAS Cube
…

Report Objects (Reporting Services)

Object Type	Convention		Description
Shared Data Source	SharedObjectTypeNameOfSource	SharedDataSourceDWPubs	A DataSource that is shared between reports
Local Data Source	ObjectTypeNameOfSource	DataSourceDWPubs	A DataSource that is part on a individual report
Shared Data Set	SharedObjectTypeNameOfSubject	ShareDataSetSalesByTitles	A DataSet that is shared between reports
Local DataSet	ObjectTypeNameOfSource	DataSetSalesByTitles	A DataSet that is part on a individual report
Report Component	ObjectTypeNameOfSubject	TableSalesByTitles	A SSRS Report Component
…

ETL Staging Objects

ETL Staging Database Tables

None

ETL Staging Database Views

None

ETL Staging Database Stored Procedures

None

Team Members

This table identifies the team members available to support the BI Solution.

Team Member Roles

This table defines the roles in which the identified team members will support the BI solution.

Estimated Schedule

Based on the defined BI Solution expectations, the estimated schedule addresses the high-level tasks required to accomplish the overall objectives.

Detailed Estimated Hours

The detailed estimated hours compose the expected level of effort for the specific tasks required under the high-level activities. Much of the technical work is expected to take a standard level of effort, but in attempting to address the needs of various potential customers, the investigation phase is expected to take an extended period of time to identify multiple analytical needs and integration needs from disparate sources.

Given the potential complexity of the data needs, the level of effort to developing the ETL integrating various data sources is also estimated to be extended beyond standard solutions.  This would include direct access to bank systems, exported data of different formats, and user-populated input.

Data Warehouse Objects

The data warehouse objects table defines the detailed physical data structure accommodating the key data components supporting the necessary BI analytics. This includes detailed information regarding the individual transactions to be tracked and dimensions by which the customer would benefit by viewing the data.

Database Schema

The database schema shows the SQL server structure containing the defined data warehouse objects.

 Data Relationships

The data relationships defines further constraints on the relations under the schema.

Data Warehouse Objects

Data Warehouse Tables

Table Name: tblTransactions

Description: Holds fact about transactions

Columns

Column Name	Data Type	Example	Max Value	Min Value	Invalid Examples
TransactionID	Int	1	Int Max	Int Min	A
PartyID	Int	1	Int Max	Int Min	A
BankAccountID	Int	1	Int Max	Int Min	A
TransactionDate	Date	1/1/2000	Date Max	Date Min	A
TransactionAmount	Decimal(18,2)	1	Int Max	1	A, -1
Comments	Nvarchar(255)	Notes	NA	NA	NA

Constraints

Column Name	Primary Key	Foreign Key	Not Null	Check	Unique	Default
TransactionID	Yes	No	Yes	No	Yes	No
PartyID	No	Yes	Yes	No	Yes	No
BankAccountID	No	Yes	Yes	No	Yes	No

Table Name: txParties

Description: Holds data about parties to the transactions

Columns

Column Name	Data Type	Example	Max Value	Min Value	Invalid Examples
PartyID	Int	1	Int Max	Int Min	A
PartyName	nVarChar(50)	ABC Inc.	NA	N	NA

Constraints

Column Name	Primary Key	Foreign Key	Not Null	Check	Unique	Default
PartyID	Yes	No	Yes	No	Yes	No

Table Name: txBankAccounts

Description: Holds data about bank accounts

Columns

Column Name	Data Type	Example	Max Value	Min Value	Invalid Examples
BankAccountID	Int	1	Int Max	Int Min	A
BankID	Int	1	Int Max	Int Min	A
AccountName	nVarChar(50)	ABC	NA	NA	-1
AccountNameShort	nVarChar(25)	ABC	NA	NA	-1
InitialDeposit	Decimal(18,2)	1.0000	Decimal Max	> 0	-1

Constraints

Column Name	Primary Key	Foreign Key	Not Null	Check	Unique	Default
BankAccountID	Yes	No	Yes	No	Yes	No
BankID	No	Yes	Yes	No	No	No

Table Name: txBanks

Description: Holds data about Banks holding the accounts

Columns

Column Name	Data Type	Example	Max Value	Min Value	Invalid Examples
BankID	Int	1	Int Max	Int Min	A
BankName	nVarChar(50)	ABC	NA	NA	-1

Constraints

Column Name	Primary Key	Foreign Key	Not Null	Check	Unique	Default
BankID	Yes	No	Yes	No	Yes	No

Table Name: DimDates

Description: Holds data about dates

Columns

Column Name	Data Type	Example	Max Value	Min Value	Invalid Examples
DateKey	Int	1	Int Max	Int Min	A
Date	Datetime	01/01/1900	Datetime Max	Datetime Min	-1, A
DateName	nVarChar(50)	ABC	NA	NA	-1
Month	Int	1	Int Max	Int Min	A
MonthName	nVarChar(50)	ABC	NA	NA	-1
Quarter	Int	1	Int Max	Int Min	A
QuarterName	nVarChar(50)	ABC	NA	NA	-1
Year	Int	1	Int Max	Int Min	A
YearName	nVarChar(50)	ABC	NA	NA	-1

Constraints

Column Name	Primary Key	Foreign Key	Not Null	Check	Unique	Default
DateKey	Yes	No	Yes	No	Yes	No

Data Warehouse Views

View Name:

Description:

Columns

Column Name	Source	Example	Max Value	Min Value	Invalid Examples

View Name:

Description:

Columns

Column Name	Source	Example	Max Value	Min Value	Invalid Examples

Data Warehouse Stored Procedures

Stored Procedure Name:

Description:

Input Parameters

Column Name	Data Type	Example	Max Value	Min Value	Invalid Examples

Output Parameters

Column Name	Data Type	Example	Max Value	Min Value	Invalid Examples

Selected Values

Column Name	Data Type	Example	Max Value	Min Value	Invalid Examples

Return Codes

ID	Status	Description

Error Codes

ID	Error Name	Error Message

OLAP Cubes Database Objects

OLAP Cubes

Cube Name:

Description:

Measure Name	Data Type	Example	Max Value	Min Value	Description

Dimensions

Dimension	Dimension Key

OLAP Dimensions

Dimension Name:

Description:

Attribute Name	Data Type	Example	Max Value	Min Value	Description

Dimension Name:

Description:

Attribute Name	Data Type	Example	Max Value	Min Value	Description