1 / 32

Introduction to MSBI & DWH by QuontraSolutions

MSBI online training offered by Quontra Solutions with special features having Extensive Training will be in both MSBI Online Training and Placement. We help you in resume preparation and conducting Mock Interviews. Emphasis is given on important topics that were required and mostly used in real time projects. Quontra Solutions is an Online Training Leader when it comes to high-end effective and efficient IT Training. We have always been and still are focusing on the key aspect which is providing utmost effective and competent training to both students and professionals who are eager to enrich their technical skills. Training Features at Quontra Solutions: We believe that online training has to be measured by three major aspects viz., Quality, Content and Relationship with the Trainer and Student. Not only online training classes are important but apart from that the material which we provide are in tune with the latest IT training standards, so a student has not to worry at all whether the training imparted is outdated or latest. Course content: • MSBI Introduction • Integration services (SSIS) • Analysis services (SSAS) • Reporting services(SSRS) Please Visit us for the Demo Classes, we have regular batches and weekend batches. QUONTRASOLUTIONS 204-226 Imperial Drive, Rayners Lane, Harrow- HA2 7HH. Email: info@quontrasolutions.co.uk Web: http://www.quontrasolutions.co.uk Phone : 44(0) 20 3734 1498 / 99

quontrasolutionsuk
Download Presentation

Introduction to MSBI & DWH by QuontraSolutions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. An Introduction to MSBI & DWH Presented By Quontra Solutions Email:info@quontrasolutions.co.uk Contact:+44 (0)20-3734-1498 / 99 Website: www.quontrasolutions.co.uk

  2. This Presentation will Helps you to understand the following • What Is ERP, In what way ERP & BI Helps a Business • What is Data warehousing ?? with different examples. How BI works with data warehouse • What is MSBI ?? • Different BI Tools in the Market • Understand the terminologies of Data warehouse (DWH)

  3. Lets Understand this first • What is DataBase & Role of Database in any Business. • What is ERP ?? What is OLTP ?? • What are master & Transaction tables ?? • What is DWH ?? What is OLAP ?? • What are reports and why it is so important ?? • What are aggregations and how it is useful for Analysis. • What is ETL ? What is the use of ETL in DWH ?

  4. What is DataBase (DB) 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.

  5. What is ERP & OLTP 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.

  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

  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

  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 ??

  9. Which are our lowest/highest margin customers ? Who are my customers and what products are they buying? What is the most effective distribution channel? What product prom--otions have the biggest impact on revenue? Which customers are most likely to go to the competition ? What impact will new products/services have on revenue and margins? Data warehouse helps any Business in Many Ways Let’s say A producer wants to know….

  10. What is DWH & OLAP 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.”

  11. 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.

  12. OLTP Vs OLAP 1. OLTP (on-line transaction processing) 1. OLAP (on-line analytical processing) 2. Day-to-day operations: purchasing, inventory, banking, manufacturing, payroll, registration, accounting, etc. 2. Data analysis and decision making 3. The tables are in the Normalized form. 3. The tables are in the De-Normalized form. 4. We Called the Storage objects as Dimension and Facts. i.e., All the masters Are dimension and the Transactions are Facts. 4. We Called the Storage objects as Tables. i.e., All the masters and the Transactions are stored in the tables. 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

  13. OLTP OLAP Masters Dimensions Normalized Tables De-Normalized Tables Topics Later We will Cover 1. Types of Dimensions 2. Slowly changing Dimensions 3. Hierarchies

  14. OLTP OLAP Transactions Facts Reference keys of Dimensions Numeric fields called as Fact or measure Qty*Unit_Price+Tax=Total Amount Usually calculate all the calculations before storing into OLAP

  15. ROLAP MOLAP Dimensions & Facts Cubes STAR Schema

  16. NOW WHAT IS OLAP ? Dimensions Facts Cubes MOLAP ROLAP

  17. MOLAP SCHEMA’S Star Snow Flake 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 Low but required Less storage space. 3. Performance is fast but required huge storage space.

  18. NOW WHAT IS DWH ? 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]

  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

  20. Also Data Mining works with Warehouse Data Data Warehousing provides the Enterprise with a memory • Data Mining provides the Enterprise with intelligence

  21. NOW WHAT IS MSBI ? What is BI : Implementing a data warehouse on any business to provide the Analysis and decision making solution using some set of tools is called Business Intelligence. Now what is MSBI : Microsoft introduced a complete tool kit inside the SQL SERVER to implement Data warehoue.

  22. The Different Roles of BI Developers Administrator :The person who can manage the complete administration part of the Data warehouse, similar to DBA. BI Architect : The person who designs the complete architecture of Data Warehouse for any business. ETL Developer : The person who works on SSIS or any other ETL Tools like Informatica. Cube Developer/OLAP Developer : The person who works on SSAS Cube creations and MDX language. Report Developer : The person who works on SSRS Reports or similar tools like BO , Crystal reports etc.,

  23. Why Only MSBI ? Oracle 10g IBM DB2 Base Product $ 25K $ 40K $ 25K

  24. Tuning $3K Diagnostics $3K Partitioning $10K Performance Expert $10K (included) Manageability Base Product $ 25K $ 40K $ 56K $ 25K $ 35K

  25. DB2 OLAP $35K DB2 Warehouse $75K Cube Views $9.5K OLAP $20k Mining $20k BI Bundle $20k Business Intelligence (included) Manageability Base Product $ 25K $ 56K $ 154.5K $ 35K $ 116K

  26. Data Guard $116K Recovery Expert $10k High Availability Business Intelligence (included) Manageability Base Product $ 25K $ 232K $ 154.5K $ 164.5K $ 116K

  27. $116K - $232K $164.5K Multi-core High Availability Business Intelligence (included) Manageability Base Product $ 25K $ 232K $ 164.5K $ 329K $348k - $464k

  28. Reporting, OLAP, Data Mining Data Analysis Data Storage Repository Middleware (Populations-Tools) Data-Migration OperationalData Sources DWH ARCHITECTURE

  29. Additional Benefit Number of Users FRONTEND TOOLS What happened? Reporting Why did it happen? Interactive OLAP What happened why and how? Ad hoc-Queries What will happen? Data Mining

  30. PRACTICAL SCENARIO OLTP O L A P ROLAP MOLAP Stage DB Optional CUBE SSAS Data Marts SSIS SSIS SSRS Analysis Services Integration Services Reporting Services

  31. DWH TERMINOLOGIES OLTP – Online Transaction Processing OLAP – Online Analytical Processing MOLAP – Multidimensional OLAP ROLAP – Relational OLAP HOLAP – Hybrid OALP Dimensions – De-normalized master tables Attributes – Columns of Dimensions Hierarchies – sequential order of attributes Facts (Measure group) – Transactions tables in DWH Fact (Measures) Cubes – Multidimensional storage of Data KPI’s – Key performance indicator Dashboards – combination of reports,kpis,charts Data Marts – Subjective Collection of Data SCD’s – Slowly changing Dimensions Perspectives – Child Cube

  32. THANK YOU

More Related