Business intelligence (BI) is a collection of technologies aimed for gathering, analysing, and providing organisations the knowledge to make better informed business decisions.
Microsoft Business Intelligence applications integrate a wide variety of BI capabilities, including: data integration, reporting, online analytical processing (OLAP), forecasting, and data mining.
For the benefit of my students and for those who wish to self-study this field, this page provides a coverage of several Microsoft BI tools in a detailed, logical and well organized manner. In addition, since Microsoft BI development environments are graphical-oriented, most of these slides include many step-by-step tutorials.
Microsoft SSIS is a data integration, migration and transformation solution. Using the SSIS developers have the ability to define heterogeneous data sources and destinations where data can then be migrated via pipeline that enables powerful capabilities and transformation options. The first two slides of this section illustrate the core concepts of a Data Warehouse, rest of the slides in this section describe different SSIS capabilities.
- DWH Basics – This presentation covers the following topics: Introduction to basic concepts of Data Warehousing, the differences between DWH and OLTP, Normalization, Snowflake Schema and Star schema, Data Granularity, Auditing.
- DWH Design Considerations – This presentations covers the following topics: Slowly changing dimensions, Indexing the Data warehouse, Data Compression, Using Partitions, Identifying Fact and Dimension tables.
- SSIS Basics – This presentations covers the following topics: SSMS export & import wizard, SSIS Basics, simple SSIS example.
- SSIS Data Flow Tasks – This presentations covers the following topics: Using the Connection Manager, Derived Column Transformation, Aggregate Task Transformation, Lookup Transformation, Merge Join Transformation, Merge Transformation, Union All Transformation, Multicast Transformation, Conditional Split Transformation, Data Conversion Transformation.
- SSIS Control Flow – This presentation covers the following topics: Precedence Constraints, Sequence Containers, using For Each and For loops, using Parameters.
- Designing and Troubleshooting SSIS – This presentation covers the following topics: Output Window, Data Viewers, Breakpoints, Watching Variables, Logging, Event Handler.
- Incremental ETL Processing – using the Slowly Changing Dimensions transformation.
- Deployment – This presentation covers the following topics: Create SSIS Catalog, Deploy a Project, Schedule a Job.
Microsoft SSRS is a server-based report generation software. The SSRS lets you create different types of reports from various data sources and incorporate many data visualization capabilities.
- Getting Started with SSRS – Creating new reports, Adding items to the report, Arranging items, formatting items, saving the report.
- Data Sources – Create a new datasource, map report to a dataset, create new table, embedded datasets.
- Controlling Header Rows – Making header rows appear in each page, Keeping header rows visible when scrolling.
- Numbers of Row per Page – Using RowNumber, Changing the page size, creating groups, changing the page size, using parameters.
- Groups – Adding a parent group, Formatting, Nested Groups.
- Calculated Fields – Creating calculated fields, conditional functions, text function, date functions, concatenating text.
- Conditional Formatting – Using expressions to create conditions, using IF and SWITCH.
- Gauges – Adding a Gauge to a table, scales, Ranges and pointers.
- SSRS Parameters – Using parameters, parameter values, drop down list parameters, using nulls, cascading parameters.
- SSRS Matrix – Create new matrix, adding calculations, adding sub groups.
Microsoft SSAS is an online analytical processing, reporting, and data mining tool. In SSAS the data can be converted into a cube for use in data analysis, SSAS also provides many data mining capabilities.
- SSAS Basics – This presentation covers the following topics: Creating new SSAS project, Create a Data Source, Data Source View, Named Calculation, Explore Data, Friendly Name, Logical Table / Named Query, Logical Primary Key, Creating New Diagram
- Cubes & Hierarchies – This presentation covers the following topics: Creating Cube, Publishing an SSAS Project, Cube Structure, Cube Basic Browsing, New Attribute from Column, Hierarchies, Creating Hierarchies, Using Hierarchies.
- SSAS Attributes – This presentation covers the following topics: Order Attributes, Attribute Relationships, Attribute KeyColumns.
- Dimension Relationship – This presentation covers the following topics: Indirect Relationship, Many to Many Relationship, Self Referencing Relationship.
- Data Mining is SSAS – This presentation covers the following topics: Data Mining Process, Decision Trees, Clustering, Time Series, Association rules, Create New Mining Structure, Mining Model Viewer, Dependency Network, Mining Accuracy Chart, Mining Model Prediction, Singleton Query.
- Power Pivot & Power View – This presentation covers the following topics: Getting Started, Importing Data, Calculated Fields and Columns, KPI, Linked Tables, Pivot Tables, Pivot Charts, Slicers and Management Dashboards, Power View.
