Study material for exam 70-466 Implementing Data Models and Reports Preparation Materials

By: Daniel Calbimonte   |   Comments (4)   |   Related: More > Professional Development Certifications

Problem

I would like to know what books, videos, links and advice you could give me to pass SQL Server exam 70-466?

Solution

There is no Training Kit for this exam (at least not yet), the official Microsoft course for this certification (Course 20466: Implementing Data Models and Reports with Microsoft® SQL Server®) is very useful, but it is not enough to pass the exam (the official courses never cover all the Microsoft certification exam topics).

So, what can we do to pass this exam?

This tip will give you links and tips for this very complex and difficult exam.

If you are a beginner in SQL Server Business Intelligence or you do not have experience in the Microsoft Certification exams, we strongly recommend taking exam 70-463 first. The 70-466 exam is very complex and difficult.

If you already passed exam 70-463, this tip is for you. We will give you advice, books and useful links for your exam.

Getting started

Here we compiled the links to SQL Server web sites, books, manuals:

Design dimensions and measures

• Given a requirement, identify the dimension/measure group relationship that should be selected.
  • SQL Server Analysis Services (SSAS) Tutorial.
  • SQL Server Analysis Services Interview Questions Part I.
  • SSAS Interview Questions on Measures, Actions, and Storage.
• Design patterns for representing business facts and dimensions (many-to-many relationships).
  • Using Many-to-Many Relationships in Multidimensional SQL Server Analysis Services.
  • SSAS Interview Questions on Measures, Actions, and Storage. Design dimensions to support multiple related measure groups (many related fact tables. What is the purpose of Dimension Usage settings? Explain different types of relationships between Facts and Dimensions.
  • Defining a Many-to-Many Relationship
• Handle degenerate dimensions in a cube.
  • SQL Server Analysis Services Interview Questions Part II - Dimensions - What is a Degenerate Dimension in SQL Server Analysis Services? In what scenarios do you use it?
  • Data Warehousing: Degenerate Dimensions.
  • Defining a Fact Relationship.
• Identify the attributes for dimensions.
  • Add an Attribute to a Dimension.
  • Dimension Attribute Properties Reference.
  • SQL Server Analysis Services Interview Questions Part II - Dimensions
• Identify the measures.
  • Measures and measure groups.
  • SSAS Interview Questions on Measures, Actions, and Storage.
• Aggregation behavior for the measures.
  • SQL Server Analysis Services Aggregation Designs.
  • Define Semi additive Behavior
• Build hierarchies.
  • SQL Server Analysis Services Attribute Relationships.
  Define granularity of dimension relationships.
  • SQL Server Analysis Service Levels of Granularity.

Implement and configure dimensions in a cube

• Translations.
  • SQL Server Analysis Services Interview Questions on Dimensions, Hierarchies and Properties - What are Dimension Translations?
  • Defining and Browsing Translations
• Define attribute relationships.
  • SQL Server Analysis Services Attribute Relationships.
  • Attribute Relationships.
• Implement hierarchies.
  • Creating a Hierarchy.
• Implement SQL Server Analysis Services (SSAS) dimensions and cubes.
  • Build a Cube From an Existing Data Source Using SQL Server Analysis Services.
  • Multidimensional Modeling (Adventure Works Tutorial).
• Create the Attribute Relationships that should be made for a given set of attributes in a dimension.
  • Lesson 5: Defining Relationships Between Dimensions and Measure Groups.
  • SQL Server Analysis Services Attribute Relationships.
• Develop new custom attributes on dimensions.
  • Custom Attribute Names in Role Playing Dimensions for SSAS.
  • Add an Attribute to a Dimension.
• Detect possible design flaws in attribute relationships.
  • Analysis Service Performance Guide
• Implement time dimensions in cubes.
  • Built in Time Dimension and Time Intelligence in SQL Server Analysis.
  • SSAS Time Account Dimension Intelligence.
  • Create a Time Dimension by Generating a Time Table.
• Manage SSAS parent-child dimensions.
  • Parent-Child Dimensions.
  • SQL Server Analysis Services Interview Questions Part II - Dimensions.
• Dimension types.
  • SQL Server Analysis Services Interview Questions Part II - Dimensions.

Design a schema to support cube architecture

• Multidimensional modeling starting from a star schema.
  • Creating a Star Schema Using a Data Source View.
  • Designing the Star Schema Database
• Relational modeling for a data source view.
  • DWH Schemas
  • Introduction to the SQLBI Methodology
• Choose or create a topology.
  • DWH Schemas
• Identify the appropriate data types with correct precision and size.
  • Data Types in Analysis Services.
  • Tips for Optimizing SQL Server OLAP/Analysis Services: Data Warehouse and Datamart Performance.

Create and configure measures

• Logically group measures and configure Measure Group Properties.
  • SSAS - Best Practices and Performance Optimization - Part 3 of 4.
  • Measures and Measure Groups.
• Select appropriate aggregation functions.
  • Use Aggregate Functions.
• Format measures.
  • Configure Measure Properties.
• Design the measure group for the correct granularity
  • SQL Server Analysis Service Levels of Granularity.
  • Defining Dimension Granularity within a Measure Group.

Implement a cube

• Use SQL Server Data Tools - Business Intelligence (SSDT-BI) to build the cube.
  • Multidimensional Modeling (Adventure Works Tutorial).
  • Creating a Sample SSAS Project and Cube
• Use SSDT-BI to do non-additive or semi-additive measures in a cube.
  • Define Semiadditive Behavior.
• Define measures.
  • Defining a Cube.
  • Creating a Cube using the Cube Wizard.
• Specify perspectives.
  • Lesson 9: Defining Perspectives and Translations.
  • SQL Server Analysis Services Perspectives.
• Define dimension usage.
  • Dimension Relationships.
• Define cube-specific dimension properties.
  • Lesson 4: Defining Advanced Attribute and Dimension Properties.
• Define measure groups.
  • Create Measures and Measure Groups.
• Implement reference dimensions.
  • Dimension Relationships.
• Implement many-to-many relationships.
  • Dimension Relationships.
  • Using Many-to-Many Relationships in Multidimensional SQL Server Analysis Services.
  • Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques.
• Implement fact relationships.
  • Dimension Relationships.
• Implement role-playing relationships.
  • Defining Role Playing Dimensions for SQL Server Analysis Services.
• Create and manage linked measure groups and linked dimensions.
  • Controlling the use of linked objects in SQL Server Analysis Services.
  • Define Linked Dimensions
• Create actions.
  • Lesson 8: Defining Actions.

Create Multidimensional Expressions (MDX) queries

• Identify the structures of MDX and the common functions (tuples, sets, TopCount, SCOPE, and more).
  • Working with Members, Tuples, and Sets (MDX).
  • MDX Basic Concepts
  • Multidimensional Expressions (MDX) Reference
  • SQL Server Analysis Services (SSAS) 2012 Top and Bottom Functions.
  • TopCount (MDX)
  • MDX Function Reference (MDX).
• Identify which MDX statement would return the required result.
  • MDX Function Reference (MDX)
• Implement a custom MDX or logical solution for a prepared case task.
  • How to reduce MDX code redundancy in SQL Server Analysis Services SSAS.
  • Order and Sort with MDX in SQL Server Analysis Services.
  • MDX Function Reference (MDX)
  • SQL Server Analysis Services Rank and Row Number Ordering.
  • Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 2.4 and 3.3).

Implement custom logic in a data model

• Define key performance indicators (KPIs).
  • Testing KPIs for SQL Server Analysis Services.
  • Lesson 7: Defining Key Performance Indicators (KPIs).
• Define calculated members.
  • Defining Calculated Members.
• Create relative measures (growth, YoY, same period last year).
  • Handling Relative Time Periods.
• Percentage of total using MDX.
  • Using MDX to Calculate Both Values and Percentages for Analysis Services.
  • MDX for Percent of Total for a subset of a Hierarchy.
• Named sets.
  • Create Named Sets.
• Implement ranking and percentile.
  • Rank (MDX).
  • Ranking results from MDX queries.
• Define MDX script to import partial PowerPivot model.
  • Analysis Services MDX Query Designer (Power Pivot).

Implement storage design in a multidimensional model

• Create aggregations.
  • SQL Server Analysis Services Aggregation Designs.
  • Optimize a SQL Server Analysis Services Measure Group Partition for Performance.
  • Aggregations and Aggregation Designs.
• Create partitions.
  • How To Define Measure Group Partitions in SQL Server Analysis Services SSAS.
  • Create and Manage a Local Partition (Analysis Services).
  • Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 2.2).
• Storage modes.
  • Partition Storage Modes and Processing.
  • How To Implement Proactive Caching in SQL Server Analysis Services SSAS.
• Define proactive caching.
  • How To Implement Proactive Caching in SQL Server Analysis Services SSAS.
  • Proactive Caching (Partitions).
• Manage write-back partitions.
  • SQL Server Analysis Services Partition Maintenance and Processing, Write enable partition.
  • Set Partition Writeback.
• Implement linked cubes.
  • Controlling the use of linked objects in SQL Server Analysis Services.
  • Define Linked Measure Groups.
• Implement distributed cubes.
  • Create and Manage a Remote Partition (Analysis Services).

Select an appropriate model for data analysis

• Select Tabular versus Multidimensional based on scalability needs.
  • Comparing Tabular and Multidimensional Solutions (SSAS).
  • BISM: Multidimensional vs. Tabular.
  • An Analysis Services Case Study: Using Tabular Models in a Large-scale Commercial Solution.
• Traditional hierarchical.
  • Comparing Tabular and Multidimensional Solutions (SSAS).
• Data volume.
  • Comparing Tabular and Multidimensional Solutions (SSAS).
• Select appropriate organizational BI, such as corporate BI or PowerBI, and team and personal BI needs and data status.
  • Comparing Tabular and Multidimensional Solutions (SSAS).

Manage, maintain, and troubleshoot a SQL Server Analysis Services (SSAS) database

Analyze data model performance

• Identify performance consequences of data source view design.
  • Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 2.3).
• Optimize performance by changing the design of the cube or dimension.
  • Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 2.1 and 2.2).
• Analyze and optimize performances of an MDX/DAX query.
  • Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 3.3).
  • Optimizing DAX Queries.
  • Analysis Services Query Performance Top 10 Best Practices.
• Optimize queries for huge data sets.
  • Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 3.3).
  • Optimizing DAX Queries.
  • Analysis Services Distinct Count Optimization
• Optimize MDX in the calculations.
  • Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 2.4).
• Performance monitor counters.
  • Performance Counters (SSAS).
• Select appropriate Dynamic Management Views for Analysis Services.
  • SQL Server 2012 Analysis Services (SSAS) DMVs.
  • Use Dynamic Management Views (DMVs) to Monitor Analysis Services.
  • SSAS Operation Guide, chapter 3.
• Analyze and define performance counters
  • Performance Counters (SSAS).
  • SSAS Operation Guide, chapter 3.
• Monitor growth of the cache
  • Part 5: An Introduction to SSAS Performance and SQL Sentry Performance Advisor for Analysis Services.
  • Performance Counters (SSAS), Cache.
• Define and view logging options
  • Log operations in Analysis Services.
  • SSAS - Best Practices and Performance Optimization - Part 4 of 4.

Process data models

• Define processing of tables or partitions for tabular and multidimensional models
  • Processing Options and Settings (Analysis Services).
  • Analysis Services 2005 Processing Architecture.
  • Process Tabular Model Partitions (SSAS Tabular).
• Define processing of databases, cubes, and dimensions for multidimensional models.
  • Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 4).
  • SQL Server Best Practices Article.
• Select full processing versus incremental processing.
  • Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 (section 4).
  • SQL Server Analysis Services ProcessAdd for a Partition.
  • Processing Options and Settings (Analysis Services).
• Define remote processing;
  • Remote Processing (Analysis Services).
• Define lazy aggregations.
  • SSAS Processing Mode (Lazy Aggregations).
• Automate with Analysis Management Objects (AMO) or XML for Analysis (XMLA).
  • Create SQL Server Analysis Services Partitions using AMO.
  • Developing with Analysis Management Objects (AMO).
• Process and manage partitions by using PowerShell.
  • Analysis Services PowerShell
  • Using PowerShell for SQL Server Analysis Services tasks - Part 1.
  • Automate SQL Server Analysis Services Tasks with PowerShell - Part 2

Troubleshoot data analysis issues

• Use SQL Profiler.
  • Use SQL Server Profiler to Monitor Analysis Services
• Troubleshoot duplicate key dimension processing errors.
  • SQL Server Analysis Services SSAS Processing Error Configurations.
  • You get “A duplicate attribute key has been found when processing ..” error message when processing a dimension.
• Error logs and event viewer logs of SSAS.
  • Log operations in Analysis Services.
  • Configuring the Analysis Services Query Log.
  • Log Properties.
  • Data collection for troubleshooting Analysis Services issues.
• Mismatch of data: incorrect relationships or aggregations.
  • Debugging Deployment Errors.
  • SSAS: Reference materialized dimension might produce incorrect results.
  • SSAS Operation Guide, chapter 7.3.2.
• Dynamic security issues.
  • Implement Dynamic Security by Using Row Filters.
  • Dynamic Security in SSAS Cube.
  • Analysis Services Dynamic Security.
  • SSAS Dynamic Security Issues.
• Validate logic and calculations.
  • Validating MDX Queries Without Running Them.
  • Automate MDX Query Testing.
  • SQL Server Best Practices Article: Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services.

Deploy SSAS databases

• Deployment Wizard.
  • Using the SQL Server Analysis Services (SSAS) Deployment Wizard.
  • Running the Analysis Services Deployment Wizard.
• Implement BIDS.
  • Deploy Analysis Services Projects (SSDT).
  • Deploying an Analysis Services Database into the Production Environment.
  • How to: Build and Deploy an Analysis Services Project.
• Deploy SSMS.
  • Deploy Model Solutions Using XMLA.
• Test solution post deployment.
  • SQL Server : Unit Testing SQL Server OLAP Cubes Using C#
  • SSAS Operation Guide, chapter 4.
• Decide whether or not to process.
  • Avoid Common SQL Server Analysis Services Problems.
• Test different roles.
  • SQL Server Analysis Services – Testing Cube Roles.
  • Configuring permissions for SQL Server Analysis Services.

Install and maintain an SSAS instance

• Install SSAS.
  • Install Analysis Services in Tabular Mode.
  • Install SQL Server 2012 from the Installation Wizard (Setup).
• Install development tools.
  • SQL Server Data Tools (SSDT) is Missing After Installing SQL Server 2014.
  • Download SQL Server Data Tools (SSDT or SSDT-BI).
• Identify development and production installation considerations.
  • Deploying Analysis Services Development and Production Systems
• Upgrade SSAS instances.
  • Upgrade Analysis Services
• Define data file and program file location.
  • SSAS Operation Guide, chapter 2.5.
  • Analysis Services Configuration - Data Directories.
• Plan for Administrator accounts.
  • SSAS Operation Guide, chapter 5.4
• Define server and database level security.
  • SSAS Operation Guide, chapter 5.
• Support scale-out read-only.
  • SSAS Operation Guide, chapter 7.5
• Update SSAS (service packs).
  • How to obtain the latest service pack for SQL Server 2012.
  • How to obtain the latest service pack for SQL Server 2014.
  • KB2755533 How to obtain the latest SQL Server 2012 service pack
  • KB2527041 How to obtain the latest SQL Server 2008 R2 service pack
  • Microsoft SQL Server support lifecycle
  • KB321185 How to determine the version and edition of SQL Server and its components
  • KB957826 The builds for all SQL Server versions
  • KB822499 Naming schema and Fix area descriptions for SQL Server software update packages
  • KB824684 Description of the standard terminology that is used to describe Microsoft software updates
• Install and maintain each instance type of Analysis Services including PowerPivot.
  • SSAS Operation Guide, chapter 8.
  • PowerPivot Server Administration and Configuration in Central Administration.
  • SSAS Tabular-Part 3: Tabular Model Administration.
• Restore and import PowerPivot.
  • Power Pivot and SSAS (Tabular) Restore
• Back up and restore by using PowerShell.
  • Automate SQL Server Analysis Services Tasks with PowerShell - Part 2.
  • Backup-ASDatabase cmdlet.
  • Restore-ASDatabase cmdlet.

Build a tabular data model

Configure permissions and roles in a tabular model

• Configure server roles.
  • Installing Analysis Services 2012 - Tabular Model.
• Configure SSAS database roles.
  • Roles and Permissions (Analysis Services).
  • Securing the Tabular BI Semantic Model, Managing roles and permissions
• Implement dynamic security (custom security approaches).
  • Implement Dynamic Security by Using Row Filters.
  • Securing the Tabular BI Semantic Model, Dynamic security
• Role-based access.
  • Roles (SSAS Tabular)
  • Securing the Tabular BI Semantic Model, Managing roles and permissions
• Test security permissions.
  • Securing the Tabular BI Semantic Model
• Implement cell-level permissions.
  • Roles (SSAS Tabular).

Implement a tabular data model

• Define tables, import data.
  • Getting started with Tabular Model in SQL Server 2012 - Part 1
  • Lesson 2: Add Data
• Define calculated columns.
  • Lesson 6: Create Calculated Columns.
• Define relationships.
  • Lesson 5: Create Relationships.
• Define hierarchies and perspectives.
  • Getting started with the SQL Server 2012 Tabular Model - Part 2
  • Lesson 9: Create Perspectives.
  • Lesson 10: Create Hierarchies.
• Manage visibility of columns and tables.
  • Getting started with the SQL Server 2012 Tabular Model - Part 2
• Embed links.
  • Getting started with the SQL Server 2012 Tabular Model - Part 2
• Optimize BISM for Power View.
  • Optimizing High Cardinality Columns in VertiPaq
  • Power View: Explore, visualize, and present your data
• Mark a date table.
  • Getting started with the SQL Server 2012 Tabular Model - Part 2
  • Lesson 4: Mark as Date Table.
• Sort a column by another column.
  • Sorting Dates Columns in PowerPivot v2 and BISM Tabular.

Implement business logic in a tabular data model

• Implement measures and KPIs.
  • Getting started with the SQL Server 2012 Tabular Model - Part 2
  • Lesson 8: Create Key Performance Indicators
• Implement Data Analysis Expressions (DAX).
  • Getting started with the SQL Server 2012 Tabular Model - Part 2
  • Data Analysis Expressions (DAX) Reference
  • DAX in the BI Tabular Model Whitepaper and Samples
• Define relationship navigation.
  • Relationships (SSAS Tabular)
  • Many-to-Many Relationships in DAX.
• Implement time intelligence.
  • SQL Server Analysis Service DAX Time Intelligence.
  • Time Intelligence Functions (DAX)
• Implement context modification.
  • Understanding the SQL Server 2012 BI Semantic Model (BISM)
  • Filter Functions (DAX)

Implement data access for a tabular data model

• Manage partitions.
  • Getting started with the SQL Server 2012 Tabular Model - Part 2
  • Lesson 11: Create Partitions
• Processing.
  • Processing tabular models 101
• Select xVelocity versus DirectQuery for data access.
  • Tabular query modes: DirectQuery vs. In-Memory.

Build a report with SQL Server Reporting Services (SSRS)

Design a report

• Select report components (crosstab report, Tablix).
  • Tablix Data Region (Report Builder and SSRS).
  • SQL Server 2012 Tutorials: Reporting Services.
• Design chart.
  • SQL Server Reporting Services Charts Tips.
• Data visualization components.
  • Create a Simple Report with the Wizard.
• Design report templates (Report Definition Language).
  • Report Definition Language (SSRS).
• Identify the data source and parameters.
  • SQL Server Reporting Services Parameters Tips.
  • Data Connections, Data Sources, and Connection Strings (SSRS).
• Design a grouping structure.
  • Understanding Groups (Reporting Services).
• Drill-down reports.
  • Drilldown Action (Report Builder and SSRS)
  • SQL Server Reporting Services 2012 Drilldown Features.
  • Drill Down Techniques in SSRS with the Visibility Property
• Drill-through reports.
  • SQL Server Reporting Services Drill Through Tips.
  • Drillthrough Reports (Report Builder and SSRS).
• Determine if any expressions are required to display data that is not coming directly from the data source.
  • Data Connections, Data Sources, and Connection Strings (SSRS), Expression based connection strings.

Implement a report layout

• Formatting.
  • Lesson 5: Formatting a Report (Reporting Services)
  • SSRS Tutorial videos.
• Apply conditional formatting.
  • SQL Server Reporting Services Expressions Tips and Tricks.
• Page configuration.
  • Pagination in Reporting Services (Report Builder and SSRS)
• Implement headers and footers.
  • Add or Remove a Page Header or Footer (Report Builder and SSRS).
• Implement matrixes, table, chart, images, list, indicators, maps, and groupings in reports.
  • Creating an SSRS Map report with data pinpoints.
  • Display Graphics Dynamically in SQL Server Reporting Services.
  • Report Builder Table or Matrix Wizard.
  • SQL Server Reporting Services Images Tips.
  • Indicators (Report Builder and SSRS).
  • Lists (Report Builder and SSRS).
• Use Report Builder to implement a report layout.
  • Page Layout and Rendering (Report Builder and SSRS).
• Create a range of reports using different data regions.
  • Data Regions and Maps (Report Builder and SSRS).
• Define custom fields (implementing different parts of the report).
  • Dataset Fields Collection (Report Builder and SSRS).
• Implement collections (global collections).
  • Built-in Globals and Users References (Report Builder and SSRS).
• Define expressions.
  • Expressions (Report Builder and SSRS).
• Implement data visualization components.
  • Tables, Matrices, and Lists (Report Builder and SSRS).
• Identify report parts.
  • Report Design View (Report Builder)
• implement group variables and report variables.
  • Report and Group Variables Collections References (Report Builder and SSRS).
• Design for multiple delivery extension formats.
  • Interactive Functionality for Different Report Rendering Extensions (Report Builder and SSRS).

Configure authentication and authorization for a reporting solution

• Configure server-level and item-level role-based security.
  • SQL Server Reporting Services 2012 Permissions.
  • Security and Protection (SSRS).
• Configure reporting service security (setup or addition of role).
  • SQL Server Reporting Services 2012 Permissions.
• Authenticate against data source.
  • Specify Credential and Connection Information for Report Data Sources.
• Store credential information.
  • Specify Credential and Connection Information for Report Data Sources.
• Describe Report Server security architecture and site level security.
  • OVERVIEW OF SQL SERVER REPORTING SERVICES 2012 ARCHITECTURE, FEATURES, AND TOOLS.
  • Authorization in Reporting Services.
• Create system level roles, item level security.
  • Predefined Roles.
  • SQL Server Reporting Services 2012 Permissions.
• Create a new role assignment.
  • SQL Server Reporting Services 2012 Permissions.
• Assign Windows users to roles.
  • SQL Server Reporting Services 2012 Permissions.
• Secure reports using roles.
  • SQL Server Reporting Services 2012 Permissions.
• Configure SharePoint groups and permissions.
  • Set Permissions for Report Server Items on a SharePoint Site (Reporting Services in SharePoint Integrated Mode)
• Define varying content for different role memberships.
  • SQL Server Reporting Services 2012 Permissions.

Implement interactivity in a report

• Drilldown.
  • Drilldown Action (Report Builder and SSRS)
  • SQL Server Reporting Services 2012 Drilldown Features.
  • Drill Down Techniques in SSRS with the Visibility Property
• Drillthrough.
  • Drillthrough Reports (Report Builder and SSRS).
• Interactive sorting.
  • Interactive Sorting for a SQL Server Reporting Services Report.
• Parameters: (databound parameters, multi-value parameters).
  • Working With Multi-Select Parameters for SSRS Reports.
• Create dynamic reports in SSRS using parameters.
  • Creating Dynamic Report Filters in SQL Server Reporting Services SSRS.
• Implement show/hide property.
  • Hide an Item (Report Builder and SSRS).
• Actions (jump to report).
  • Add a Bookmark to a Report (Report Builder and SSRS)
• Filters.
  • Creating Dynamic Report Filters in SQL Server Reporting Services SSRS.
• Parameter list.
  • SQL Server Reporting Services Using Multi-value Parameters.
• Fixed headers.
  • SQL Server Reporting Services Report Manager Tips and Tricks
• Embedded HTML.
  • Add HTML into a Report (Report Builder and SSRS)

Troubleshoot reporting services issues

• Query the ReportServer database.
  • Retrieve SSRS report server database information.
• View Reporting Services log files.
  • Reporting Services Logging.
  • SQL Server Reporting Services (SSRS) Log Files for Troubleshooting.
• Use Windows Reliability and Performance monitor data for troubleshooting.
  • Using PerfMon for SQL Server Reporting Services Performance Management.
  • Performance Counters for the ReportServer:Service and ReportServerSharePoint:Service Performance Objects.
• Use the ReportServer: define service and web service objects.
  • Report Server Web Service.
• Monitor for long-running reports.
  • Controlling dataset processing time in SQL Server Reporting Services reports.
  • Optimize Report Parameter Dropdowns in SQL Server Reporting Services.
  • Reporting Services Performance and Optimization.
• Rendering, and connectivity issues.
  • SQL Server Reporting Services 2012 SP2 CU3 Report Rendering Issues.
  • Troubleshooting Reports: Report Performance.
• Use SQL Profiler.
  • Using SQL Profiler for SSRS Reports - Missing main dataset SQL Query in trace.
• Perform data reconciliation for incorrect relationships or aggregations.
  • TESTING SSRS REPORTS
• Detect dynamic security issues.
  • SQL Server Reporting Services Security Tips
• Validate logic and calculations.
  • SQL Server Reporting Services Best Practices Tips.

Manage a report environment

• Manage subscriptions and subscription settings.
  • Subscriptions and Delivery (Reporting Services).
• Define data-driven subscriptions.
  • Data-Driven Subscriptions.
• Manage data sources.
  • Data Sources Supported by Reporting Services (SSRS).
• Integrate SharePoint Server.
  • Install Reporting Services SharePoint Mode for SharePoint 2013.
• Define email delivery settings.
  • Configure a Report Server for E-Mail Delivery (Reporting Services).
• Manage the number of snapshots.
  • Working with Report Snapshots in SQL Server Reporting Services SSRS.
  • Creating, Modifying, and Deleting Snapshots in Report History.
• Manage schedules.
  • Schedules.
• Running jobs, and report server logs.
  • Manage a Running Process.
  • Report Server Execution Log and the ExecutionLog3 View.
• Manage report server databases.
  • Administer a Report Server Database.
• Manage the encryption keys.
  • SQL Server Reporting Services (SSRS) Encryption Key.
  • Configure and Manage Encryption Keys.
• Set up the execution log reporting.
  • SQL Server Report Server 2008 R2 Execution Log Reports.
  • Report Server Execution Log and the ExecutionLog3 View.
• Review the reports.
  • SQL Server Reporting Services Best Practices Tips.
• Configure site-level settings.
  • Site Settings Page (Report Manager)
• Design report lifecycle.
  • Report Life Cycle: Creating, Managing, and Delivering Reports.
• Automate management of reporting services.
  • SQL Server Reporting Services (SSRS) RS.EXE Utility.
  • Automate SSRS Report Generation using PowerShell.
  • Custom MSBuild Tasks: Automated Report and DB Deployment.
• Create a report organization structure.
  • Reporting Services Concepts (SSRS)
• Install and configure reporting services.
  • Install Reporting Services Native Mode Report Server (SSRS)
• Deploy custom assemblies.
  • SQL Server Reporting Services Custom Code Assemblies.
  • Deploying a Custom Assembly.

Configure report data sources and datasets

• Select appropriate query types (stored procedure versus table versus text only).
  • Pros and Cons: Stored Procedures–vs.- Embedded Queries–vs.- Views in an SSRS Dataset
• Configure parameterized connection strings (dynamic connection strings).
  • Data Connections, Data Sources, and Connection Strings (SSRS), Expression based connection strings.
• Define filter location (dataset versus query).
  • MSBI(SSRS) Training --SSRS Report Parameters, Filters, Query Parameters.
  • Add a Filter to a Dataset (Report Builder and SSRS).
• Configure data source options, for example, extract and connect to multiple data sources.
  • Data Connections, Data Sources, and Connection Strings (SSRS)
• Shared and embedded data sources and datasets.
  • Create, Modify, and Delete Shared Data Sources (SSRS)
  • Embedded and Shared Datasets (Report Builder and SSRS)
• Use custom expressions in data sources.
  • Data Connections, Data Sources, and Connection Strings (SSRS), Expression based connection strings.
• Connect to Microsoft Azure SQL database.
  • SQL Azure Connection Type (SSRS)
• Connect to Microsoft Azure Marketplace.
  • SSRS Report using dataset.
  • Microsoft Azure Marketplace Language.
• Implement DAX and MDX queries to retrieve appropriate data sets.
  • Analysis Services Connection Type for MDX (SSRS)
  • Simple SSRS Report using MDX Query.
  • Write DAX queries in Report Builder.
• Work with non-relational data sources, such as XML or SharePoint lists.
  • SharePoint List Connection Type (SSRS)
  • XML Connection Type (SSRS).
• Connect to HDInsight Server.
  • SQL Server Reporting Services

Conclusion

As you can see, there are a lot manuals, links and videos to study. If you already have real life experience in these topics it usually takes 3 months to study the material. Otherwise, plan on 6 months or more.

If you believe there are other useful links for the exam feel free to comment and share your material.

Next Steps

There are several links that will be useful to you:

• Exam 70-466
• Other books
• SSAS Papers
• Microsoft Practice Exam for 70-466 MSCert: Implementing Data Models and Reports with Microsoft SQL Server.

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips