1

» Introduction to Business Intelligence

• The reasons why BI projects are launched.
• What is a data warehouse?
• The components of a data warehouse solution
• The steps of modeling a DW (Ralph Kimball)
• Understanding the principles of modeling (star, snowflake, fact constellation).
• SQL Server BI, DataWarehouse platform.
• Architecture of SQL Server 2012's BI tools.
• Demonstration¤ implementations and uses of SQL Server 2012 Business Intelligence.

2

» Data Quality and Master Data Management (MDM)

• The notion of a data quality standard.
• Goals of Master Data management (MDS). Applying management rules in order to ensure the validity of the data.
• Master Data Services.
• The Master Data Management component DQS Cleansing.
• Data deduplication.
• Example¤Overview of quality models..

3

» Integration Services (SSIS), objects worked with

• Understanding the principles and model of ETL. Overview.
• The notion of Packages, the notion of Workflows.
• Defining the control flow and the package.
• Different tasks of a control flow: an SQL script, sending emails, updating the cube.
• The “Change Data Capture“ task.
• Add-in tasks (filewatcher).
• Sequence container.
• ForEach loop container.
• Exercise ¤Creating and editing control flows.

4

» Integration Services (SSIS), knowing how to add to tables

• Sources, destinations, and transformations.
• Different transformations: Conditional split, derived column, grouping, etc.)
• Slowly changing dimensions.
• Deploying and running packages.
• Scheduling and configuring packages.
• Logging, security.
• Exercise ¤Adding to a table. Implementing transformations. Creating and using packages. Using logs.

5

» Analysis Services (SSAS), building cubes and star schemas

• Introduction to multidimensional cubes.
• SSAS tab models.
• Using dimension tables and fact tables.
• Introduction to tabular cubes and to PowerPivot.
• Creating cubes in BIDS.
• Designing the dimension.
• User hierarchies.
• Attribute relationships.
• Composite keys.
• Exercise ¤Creating an Analysis Services database. Setting up dimensions. Creating cubes.

6

» SSAS: Advanced features

• Introduction to the MDX language.
• Calculated members and named assemblies.
• Extraction and reports.
• Partitions and aggregation designs.
• DMX graphical prediction queries.
• Backing up and restoring cubes.
• Incremental updates and cube security.
• Hands-on work ¤Working with the MDX language. Writing queries Implementing simple and complex calculations. Backing up a

7

» Introduction to Data Mining

• The analytical business: Issues and approach.
• Data mining algorithms: Decision Tree, Clustering, Sequence Clustering, Naive Bayes, Association etc.
• The selection process with Data Mining.
• The CRISP model.
• Analysis tools in SSAS. Integration Services and Data Mining.
• Group discussion¤Presentation of a marketing case study.

8

» Reporting Services (SSRS): Building reports

• The report server.
• Report Designer vs Report Builder 3.0.
• Using Tablix (tables and matrices).
• Advanced formatting elements (dates and currency).
• Advanced presentation elements (conditional formatting, graphs and charts, sorting).
• Analysis elements (expressions, KPIs, grouping and data mining, totals and subtotals).
• The Report Model.
• Other reports (MDX cube reports, related subreports and reports, extracting data with DMX).
• Exercise ¤Getting started with tools. Creating, formatting, and enhancing the presentation and content of reports on the

9

» SSRS: Deploying and managing reports

• Exporting reports in Excel and PDF, Word.
• Using PowerPivot in Excel, DAX language.
• PerformancePoint.
• Configuration manager.
• Deploying reports and report management with SharePoint.
• Caching, report snapshots
• Subscriptions (sending reports by email).
• Safety
• Exercise ¤Publishing reports in different formats. Working with PowerPivot in Excel.