introduction to msbi by yasir
DESCRIPTION
A brief introduction on Data warehousing and implementing the data warehousing using MSBITRANSCRIPT
![Page 1: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/1.jpg)
By - Shaik Yasir Ahmed
![Page 2: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/2.jpg)
![Page 3: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/3.jpg)
![Page 4: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/4.jpg)
DataBase (DB) –A place where the collection of records will be maintained in a structured format so that It can be easily retrieved when ever required is known as a
database. One of the most popularly used database model is the relational model. It was developed by Edgar Codd in 1969.
Example : How do you think the Organizations store their employee and customer information? they store it in a database. where do you think the website maintains the login information about their users? they store it in a database.
![Page 5: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/5.jpg)
ERP–ERP, which is an abbreviation for Enterprise Resource Planning, is principally an integration of business management practices and modern technology.ERP is a business tool that management uses to operate the business day-in and day-out.
OLTP–OLTP, which is an abbreviation for Online Transaction processing, handle real time transactions which inherently have some special requirements. If your running a Bank, for instance, you need to ensure that as people withdrawing money from ATM’S they are properly and efficiently updating the database also those transactions are properly effecting to their Accounts.
![Page 6: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/6.jpg)
6
Data, Data everywhere yet ...
• I can’t find the data I need– data is scattered over the network
• I can’t get the data I need• need an expert to get the data
• I can’t understand the data I found• available data poorly documented
• I can’t use the data I found• results are unexpected• data needs to be transformed from one
form to other
![Page 7: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/7.jpg)
7
What are the users saying...
•Data should be integrated across the enterprise•Summary data has a real value to the organization•Historical data holds the key to understanding data over time•What-if capabilities are required
![Page 8: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/8.jpg)
8
In What way I can Answer the above question with my OLTP system...
Is Data Warehousing is the Solution ?? YES
Can I Improve my business using Data
warehousing ??
YES.. How ??
![Page 9: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/9.jpg)
9
Which are our lowest/highest margin customers ?
Which are our lowest/highest margin customers ?
Who are my customers and what products are they buying?
Who are my customers and what products are they buying?
Which customers are most likely to go to the competition ?
Which customers are most likely to go to the competition ?
What impact will new products/services have on revenue and margins?
What impact will new products/services have on revenue and margins?
What product prom--otions have the biggest impact on revenue?
What product prom--otions have the biggest impact on revenue?
What is the most effective distribution channel?
What is the most effective distribution channel?
Data warehouse helps any Business in Many WaysLet’s say A producer wants to know….
![Page 10: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/10.jpg)
DWH – (Data Warehousing)It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources. Raugh kimball – In simplest terms Data Warehouse can be defined as collection of Data marts. -Data marts : Subjective collection of Data.
Bill Inmon – A data warehouse is a “subject-oriented, integrated, time variant and nonvolatile” collection of data in support of management’s decision-making process.”
![Page 11: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/11.jpg)
OLAP – (Online Analytical Processing)The ability to analyze metrics in different dimensions such as time, geography, gender, product, etc. For example, sales for the company is up. What region is most responsible for this increase? Which store in this region is most responsible for the increase? What particular product category or categories contributed the most to the increase? Answering these types of questions in order means that you are performing an OLAP analysis.OLAP servers provides better performance for accessing multidimensional data. The most important mechanism in OLAP which allows it to achieve such performance is the use of aggregations.
Aggregations are built from the fact table by changing the granularity on specific dimensions and aggregating up data along these dimensions.
OLAP systems gives analytical capabilities that are not in SQL or are more difficult to obtain.
![Page 12: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/12.jpg)
1. OLTP (on-line transaction processing)
2. Day-to-day operations: purchasing, inventory, banking, manufacturing, payroll, registration, accounting, etc.
1. OLAP (on-line analytical processing)
2. Data analysis and decision making
3. The tables are in the Normalized form. 3. The tables are in the De-Normalized form.
5. For Designing OLTP we used data modeling.
5. For Designing OLAP we used Dimension modeling.OLAP is classified into two i.e.,MOLAP & ROLAP
4. We Called the Storage objects as Tables. i.e., All the masters and the Transactions are stored in the tables.
4. We Called the Storage objects as Dimension and Facts. i.e., All the masters Are dimension and the Transactions are Facts.
![Page 13: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/13.jpg)
Product
Prod_Id
Prod_Name
Base_Rate
Cat_IdCategory
Cat_Id
Cat_Name
Cat_Desc
Group_IdGroup
Group_Id
Group_Name
Group_Desc
Product_Dim
Prod_Id
Prod_Name
Base_Rate
Cat_Name
Cat_Desc
Group_Name
Group_Desc
Topics Later We will Cover
2. Slowly changing Dimensions1. Types of Dimensions
3. Hierarchies
Normalized Tables De-Normalized Tables
![Page 14: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/14.jpg)
SalesOrder_Fact
Cust_Id
Prod_Id
Order_Date
Delivery_Date
Unit_Price
Qty
Total_Amount
Tax
SalesOrderDetails
Cust_Id
SalesPerson
Prod_Id
Order_Date
Booked_Date
Delivery_Date
Unit_Price
Qty
Tax
Created_By Qty*Unit_Price+Tax=Total AmountUsually calculate all the calculations before storing into OLAP
Reference keys of Dimensions
Numeric fields called as Fact or measure
![Page 15: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/15.jpg)
Prod_Dim
Prod_Id
………
Cust_Dim
Cust_Id
………
Time_Dim
Date
Year
Month
………
Org_Dim
Org_Id
………SalesOrder_Fact
Cust_Id
Prod_Id
Order_Date
Delivery_Date
Org_Id
Unit_Price
Qty
Total_Amount
Tax
STAR Schema
![Page 16: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/16.jpg)
Product_Dim
Prod_Id
Prod_Name
Base_Rate
Cat_Name
Cat_Desc
Group_Name
Group_Desc
SalesOrder_Fact
Cust_Id
Prod_Id
Order_Date
Delivery_Date
Unit_Price
Qty
Total_Amount
Tax
![Page 17: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/17.jpg)
1. Dimensions will have only relation with the Fact. (Normalized model)
1. Dimension will have a relation other than Fact. (De-Normalized model)
2. One to many or One to One relation will Occur.
2. Used for many to many relation.
3. Performance is fast but required huge storage space.
3. Performance is Low but required Less storage space.
![Page 18: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/18.jpg)
18
A single, complete and consistent store of data obtained from a variety of different sources made available to end users in a what they can understand and use in a business context.
[Barry Devlin]
![Page 19: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/19.jpg)
19
Data Warehousing -- It is a process• Technique for assembling and
managing data from various sources for the purpose of answering business questions. Thus making decisions that were not previous possible
• A decision support database maintained separately from the organization’s operational database
![Page 20: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/20.jpg)
20
Also Data Mining works with Warehouse Data
Data Warehousing provides the Enterprise with a memory
Data Mining provides the Enterprise with intelligence
![Page 21: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/21.jpg)
![Page 22: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/22.jpg)
![Page 23: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/23.jpg)
Base ProductBase Product$ 25K $ 40K $ 25K
Oracle 10g
IBM DB2
![Page 24: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/24.jpg)
Base ProductBase Product
ManageabilityManageability
(included)(included)
$ 25K $ 40K $ 25K $ 56K $ 35K
Tuning $3K
Diagnostics $3K
Partitioning $10K
Performance
Expert$10K
![Page 25: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/25.jpg)
Base ProductBase Product
ManageabilityManageability
(included)(included)
$ 25K $ 35K $ 154.5K $ 56K$ 116K
Business Business IntelligenceIntelligence
OLAP $20k
Mining$20k
BI Bundle$20k
DB2 OLAP $35KDB2
Warehouse $75K
Cube Views $9.5K
![Page 26: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/26.jpg)
Base ProductBase Product
ManageabilityManageability
(included)(included)
$ 25K $ 154.5K $ 164.5K $ 232K$ 116K
Business Business IntelligenceIntelligence
High AvailabilityHigh Availability
Data Guard $116K Recovery
Expert$10k
![Page 27: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/27.jpg)
Base ProductBase Product
ManageabilityManageability
(included)(included)
High AvailabilityHigh Availability
Business Business IntelligenceIntelligence
Multi-coreMulti-core
$348k - $464k
$ 232K$ 25K $ 164.5K $ 329K
$164.5K$116K - $232K
![Page 28: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/28.jpg)
OperationalData Sources
Data-Migration Middleware (Populations-Tools)
DataStorage
RepositoryRepository
DataAnalysis
Reporting, OLAP,Data Mining
![Page 29: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/29.jpg)
Additional BenefitAdditional BenefitNumber of UsersNumber of Users
What happened?
What happened?
Why did it happen?Why did
it happen?
What will happen?
What will happen?
What happened why and how?
What happened why and how?
![Page 30: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/30.jpg)
Stage DB Optional
ROLAP
OLTP
MOLAP
O L A P
SSIS
Integration Services Analysis Services
Reporting Services
SSAS
SSRS
SSISData Marts
CUBE
![Page 31: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/31.jpg)
OLTP – Online Transaction ProcessingOLAP – Online Analytical ProcessingMOLAP – Multidimensional OLAPROLAP – Relational OLAPHOLAP – Hybrid OALP Dimensions – De-normalized master tables Attributes – Columns of DimensionsHierarchies – sequential order of attributesFacts (Measure group) – Transactions tables in DWHFact (Measures)Cubes – Multidimensional storage of DataKPI’s – Key performance indicatorDashboards – combination of reports,kpis,chartsData Marts – Subjective Collection of DataSCD’s – Slowly changing DimensionsPerspectives – Child Cube
![Page 32: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/32.jpg)
![Page 33: Introduction To Msbi By Yasir](https://reader038.vdocuments.us/reader038/viewer/2022102805/555c4f10d8b42af3448b47ea/html5/thumbnails/33.jpg)