1 / 24

MSBI,DW and Data Integration Techniques 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.

Download Presentation

MSBI,DW and Data Integration Techniques 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. MSBI, Data Warehousing and Data Integration Techniques By Quontra Solutions Email : info@quontrasolutions.com Contact : 404-900-9988 WebSite : www.quontrasolutions.com

  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 Users Data Tools Data Warehouse Extraction Data Sources

  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 MERGEdbo.myDestinationTableASdest USING ( SELECTProductID ,MIN(PurchaseDate)ASMinTrxDate ,MAX(PurchaseDate)ASMaxTrxDate FROMdbo.mySourceTable WHEREProductIDISNOTNULL GROUPBYProductID )ASsrc ONdest.ProductID=src.ProductID WHENMATCHEDTHEN UPDATESETMaxTrxDate=src.MaxTrxDate ,MinTrxDate=ISNULL(dest.MinTrxDate,src.MinTrxDate) WHENNOTMATCHEDBYSOURCETHENDELETE WHENNOTMATCHEDBYTARGETTHENINSERT(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 ;WITHmyTable(id)AS ( SELECT 1 id UNIONALL SELECTid+ 1 FROMmyTable WHEREid< 10 ) SELECT*FROMmyTable COMMON TABLE EXPRESSIONS (CTEs) are the most popular recursive constructs in T-SQL

  21. Move Rows in a single Query DECLARE@Table1TABLE (idint,namevarchar(50)) INSERT@Table1VALUES (1,'Maxwell'),(2,'Miller'),(3,'Dhoni') DECLARE@Table2TABLE (idint,namevarchar(50)) DELETEFROM@Table1OUTPUTdeleted.*INTO@Table2 SELECT*FROM@Table1 SELECT*FROM@Table2 OUTPUT clause redirects the intermediate results of UPDATE, DELETE or INSERT into a table specified

  22. Query to generate random password SELECTCHAR(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 ASidWHERE 1 = 1 IN clause expects column name at its left? Well, not Really! SELECT*FROMmyTableWHERE'searchtext'IN(Col1,Col2,Col3) Two ‘=‘ operators in single assignment in UPDATE? Possible! DECLARE@IDINT= 0 UPDATEmySequenceTableSET@ID=ID=@ID+ 1

  24. Thank you!!

More Related