MSBI, Data Warehousing and Data Integration Techniques By Quontra Solutions

1
MSBI, Data Warehousing and Data Integration
Techniques By Quontra Solutions

• Email info_at_quontrasolutions.co.uk
• Contact 020-3734-1498
• WebSite www.quontrasolutions.co.uk

2
Agenda

• What is BI?
• What is Data Warehousing?
• Microsoft platform for BI applications
• Data integration methods
• T-SQL examples on data integration

3
What is BI?
Business Intelligence is a collection of
theories, algorithms, architectures, and
technologies that transforms the raw data into
the meaningful data in order to help users in
strategic decision making in the interest of
their business.
4
BI Case
For example senior management of an industry can
inspect sales revenue by products and/or
departments, or by associated costs and incomes.
BI technologies provide historical, current and
predictive views of business operations. So,
management can take some strategic or operation
decision easily.
5
Typical BI Flow
6
Why BI?

• By using BI, management can monitor objectives
  from high level, understand what is happening,
  why is happening and can take necessary steps why
  the objectives are not full filled.
• Objectives
• Business Operations Reporting
• Forecasting
• Dashboard
• Multidimensional Analysis
• Finding correlation among different factors

7
What is Data warehousing?
A data warehouse is a subject-oriented,
integrated, time-variant and non-volatile
collection of data in support of management's
decision making process. - Bill Inmon A data
warehouse is a copy of transaction data
specifically structured for query and analysis. -
Ralph Kimball
8
Dimensional Data Model
Although it is a relational model but data would
be stored differently in dimensional data model
when compared to 3rd normal form. Dimension A
category of information. Ex. the time
dimension. Attribute A unique level within a
dimension. Ex. Month is an attribute in the Time
Dimension. Hierarchy The specification of levels
that represents relationship between different
attributes within a dimension. Ex. one possible
hierarchy in the Time dimension is Year ? Quarter
? Month ? Day. Fact Table A fact table is a
table that contains the measures of interest. Ex.
Sales Amount is a measure.
9
Data warehouse designs

• Star Schema A single object (the fact table)
  sits in the middle and is radically connected to
  other surrounding objects (dimension lookup
  tables) like a star. Each dimension is
  represented as a single table. The primary key in
  each dimension table is related to a foreign key
  in the fact table.
• Snowflake Schema An extension of the star
  schema, where each point of the star explodes
  into more points. In a star schema, each
  dimension is represented by a single dimensional
  table, whereas in a snowflake schema, that
  dimensional table is normalized into multiple
  lookup tables, each representing a level in the
  dimensional hierarchy.

10
Typical Data warehouse model
11
Data warehouse implementation

• After the team and tools are finalized, the
  process follows below steps in waterfall
• Requirement Gathering
• Physical Environment Setup
• Data Modeling
• ETL
• OLAP Cube Design
• Front End Development
• Report Development
• Performance Tuning and Query Optimization
• Data Quality Assurance
• Rolling out to Production
• Production Maintenance
• Incremental Enhancements

12
Microsoft BI Platform
13
Microsoft BI Tools
SSIS This tool in MSBI suite performs any kind
of data transfer with flexibility of customized
dataflow. Used typically to accomplish ETL
processes in Data warehouses. SSRS provides
the variety of reports and the capability of
delivering reports in multiple formats. Ability
to interact with different kind of data
sources SSAS MS BI Tool for creating a cubes,
data mining models from DW. A typical Cube uses
DW as data source and build a multidimensional
database on top of it.
14
MSBI Tools
Power View and Power Pivot These are self serve
BI tools provided by Microsoft. Very low on cost
of maintenance and are tightly coupled with
Microsoft Excel reporting which makes it easier
to interact. Performance Point Servers It
provides rapid creation of PPS reports which
could be in any form and at the same time forms
can be changed just by right click. Microsoft
also provides the Scorecards, dashboards, data
mining extensions, SharePoint portals etc. to
serve the BI applications.
15
Data Integration methods
16
Different ways of integration

• RDBMS
• Copying data from one table to another table(s)
• Bulk / Raw Insert operations
• Command line utilities for data manipulation
• Partitioning data
• File System
• Copying file(s) from one location to another
• Creating flat files, CSVs, XMLs, Excel
  spreadsheets
• Creating directories / sub-directories

17
Different ways of integration

• Web
• Calling a web service to fetch / trigger data
• Accessing ftp file system
• Submitting a feedback over internet
• Sending an email / SMS message
• Other
• Generate Auditing / Logging data
• Utilizing / maintaining configuration data
  (static)

18
T-SQL Best practices
19
Query to merge data into a table
MERGE dbo.myDestinationTable AS dest USING
( SELECT ProductID , MIN(PurchaseDate) AS
MinTrxDate , MAX(PurchaseDate) AS MaxTrxDate FROM
dbo.mySourceTable WHERE ProductID IS NOT NULL
GROUP BY ProductID ) AS src ON dest.ProductID
src.ProductID WHEN MATCHED THEN UPDATE SET
MaxTrxDate src.MaxTrxDate , MinTrxDate
ISNULL(dest.MinTrxDate, src.MinTrxDate) WHEN NOT
MATCHED BY SOURCE THEN DELETE WHEN NOT MATCHED
BY TARGET THEN INSERT (ProductID, MinTrxDate,
MaxTrxDate) VALUES (src.ProductID,
src.MinTrxDate, src.MaxTrxDate) MERGE clause is
T-SQL programmers favorite as it covers 3
operations in one
20
Query to get a sequence using CTE
WITH myTable (id) AS ( SELECT 1 id UNION
ALL SELECT id 1 FROM myTable WHERE id lt
10 ) SELECT FROM myTable COMMON TABLE
EXPRESSIONS (CTEs) are the most popular recursive
constructs in T-SQL
21
Move Rows in a single Query
DECLARE _at_Table1 TABLE (id int, name
varchar(50)) INSERT _at_Table1 VALUES (1,
'Maxwell'), (2, 'Miller'), (3, 'Dhoni') DECLARE
_at_Table2 TABLE (id int, name varchar(50)) DELETE
FROM _at_Table1 OUTPUT deleted. INTO
_at_Table2 SELECT FROM _at_Table1 SELECT FROM
_at_Table2 OUTPUT clause redirects the intermediate
results of UPDATE, DELETE or INSERT into a table
specified
22
Query to generate random password
SELECT CHAR(32 (RAND() 94)) CHAR(32
(RAND() 94)) CHAR(32 (RAND()
94)) CHAR(32 (RAND() 94)) CHAR(32
(RAND() 94)) CHAR(32 (RAND()
94)) Non-deterministic functions like RAND()
gives different result for each evaluation
23
Funny T-SQL Try it yourself ?
Aliases behavior is not consistent SELECT 1id,
1.eMail, 1.0eMail, 1eMail Ever seen WHERE clause
in SELECT without FROM clause ? SELECT 1 AS id
WHERE 1 1 IN clause expects column name at its
left? Well, not Really! SELECT FROM myTable
WHERE 'searchtext' IN (Col1, Col2, Col3) Two
operators in single assignment in UPDATE?
Possible! DECLARE _at_ID INT 0 UPDATE
mySequenceTable SET _at_ID ID _at_ID 1
24
(No Transcript)