Microsoft 70-466:Implementing Data Models & Reports

Explore an introduction to business intelligence and examine the tools available for business intelligence used within sequel server.

Explore the basics of business intelligence, data warehouses, EDL processes, and SQL Server Integration Services as introduced in this lesson.

Business intelligence provides consistent, accurate data across the organization at required granularity to support intelligent decisions, using a denormalized data warehouse for reporting and moving data from oltp to olap.

Explore data warehouses as tuned olap databases that consolidate historic data from multiple sources for reporting and forecasting, using centralized or data-mart architectures and etl processes.

Connect to a datasource, extract data, transform for consistency and accuracy, and load into an olap data warehouse to enable reports, multidimensional cubes, and tabular data projects.

Learn to perform e-tail extract transform and load using SQL Server Integration Services (SSIS), the primary, .NET-based ETL tool introduced in 2005, replacing data transformation services.

Explore SQL Server 2012 BI tools, including SSIS, Analysis Services (tabular and multidimensional), Reporting Services, Report Builder, Power Pivot, and SharePoint integration for designing data models and reports.

Explore SQL Server data tools in Visual Studio to build integration services, analysis services, and reporting services projects for business intelligence, enabling analysts to browse data with Excel and SharePoint.

Explore SQL Server components for intelligence, using SQL Server Data Tools, Excel Power Pivot, and Report Builder to select between OLTP and OLAP databases for transactional, trending, and forecasting needs.

Explore an introduction to SQL Server Reporting Services, including reporting services reports and the components within a SQL Server Reporting Services report.

Learn the basics of SSRS, including creating and delivering reports, and explore the components of Reporting Services reports.

Learn the evolution of SQL Server Reporting Services, including native and SharePoint integrated modes, report management, and exporting to formats such as Excel, Word, SML, images, and atom feeds.

Explore three report creation options—single server data tools, report builder, and Power View—and learn how reports publish to report manager or a SharePoint document library depending on installation.

Learn how SSRS reports are defined in RDL, including data sources and datasets, deployed to the report server, and rendered into formats like email, web, or Excel.

Explore grouping in SQL Server reports, including expressions, drill down and drill through, and parameters. Create table and matrix groupings in Reporting Services, then add aggregations with expressions and scope.

Reporting Services uses its own expression language to create aggregations, convert data types, compare data, provide dynamic formatting, and concatenate values, similar to Access Reports.

Explore how drilldown reports reveal data from countries to cities, using visibility to hide granularity while the full query runs; learn export limitations and Excel support.

Explore drill through reports that use separate datasets and parameters to open a new report from a click, creating smaller, focused data sets for subcategories and products.

Explore how parameters filter reports to create dynamic views with start date and end date. Define name, prompt, data type, and available values in the where clause.

Learn visual components in reporting services, including charts of various styles and embedded or external images. Use data bars, sparklines, indicators, and gauges to build dashboards.

Create sql server reporting services reports in Visual Studio, using the wizard and scratch, connect to Adventure Works 2012, and build a tabular report grouped by job title with names.

Create a tabular report in SQL Server, drag and drop fields, group by year, month, category, subcategory, sort by month number, then preview and format for a clean, centered layout.

Design and format a SQL Server report by centering headers, applying currency formatting to sales amounts, and configuring drill-down visibility for subcategories and categories to enhance readability.

Demonstrate creating SQL Server reports with drill-down, hide-on-click visibility, and parameterized navigation to a separate products report by passing the product ID.

Bind the product report to the product dataset, use a hidden id parameter with defined values, and control refresh and defaults to enable drill-through from the sales report.

Create a basic report in SQL Server Data Tools using the wizard or from scratch, covering data sources, data sets, tables, and drill down and drill through with parameters.

Prepare for the exam with hands-on reporting services, learn report components and renderings, drill down versus drill through, and RDL as the report definition language.

Explore reporting services installations, SharePoint integration, and permissions; build RDL reports with Report Builder or SQL Server Data Tools, using tables, matrix, list, charts, and drill through and drill down.

Master SSRS management by deploying reports, configuring report security, handling report execution, setting up subscriptions, and enabling data alerts.

Explore the deployment of reports and other objects with reporting services, and discuss reporter security.

Deploying reports varies by deployment type, native or SharePoint integrated. Deploy from SQL Server Data Tools or import via report manager, and download into a SharePoint library in integrated mode.

Explore the D-flat security model for reporting services with integrated authentication and, in native installations, configure report server options; in SharePoint integrated mode, permissions apply at the document library level.

The report security model uses a hierarchical structure with inherited permissions, where breaking inheritance is rare; it governs report server access, report builder use, viewing rights, and data source security.

Execute reports using push or pull methods, with caching, report snapshots, subscriptions, and data alerts; the report server assembles data from sources and renders html or sbx for the browser.

Enable report caching to store intermediate reports in the report server temp database, with on-demand or scheduled caching (default 30 minutes) that speeds final renderings and resets when parameters change.

Report snapshots store the full dataset on the report server database and preserve history, while report caches store intermediate results in the temp database without long-term history.

Configure subscriptions to deliver reports on a schedule with formats and static parameter values; leverage data driven subscriptions for multiple delivery options stored in a database table.

Define data alerts in Reporting Services 2012 to set notification rules and email settings for reports. Check data on a schedule and email reports when criteria are met.

Deploy reporting services components, configure target folders and data sources, and manage permissions and inheritance in report manager to control access and security.

Render the sales report in the browser, drill down to products and drill through to the products report, and manage shared data sources, subscriptions, caching, and report snapshots.

Learn how to manage reporting services with snapshot history and full RDL storage in the report server database, plus cache, security, and ad hoc reporting via report builder 2012.

Explore how to build and customize reporting services reports with drag-and-drop, groupings by gender and marital status, currency formatting, and drill-down analysis, while managing permissions and report history.

Review exam scenarios for data models and reports, focusing on port security, security inheritance, and SharePoint vs native installations. Understand report execution, cache and snapshot reports, and data alerts.

Compare security between SharePoint integrated and native reporting services, optimize rendering with cache reports or report snapshots stored in temp or report server databases, note data alerts for integrated mode.

Explore an introduction to cubes and the components of a multidimensional cube as part of implementing data models and reports.

Explore the introduction to cubes and how multidimensional OLAP cubes pre-calculate aggregations of facts like total sales, cost, tax, and discount, and slice by product and customer dimensions.

Identify the data source and data source view that define the tables used to populate the cube, then define measures from fact tables and dimensions from attributes for slicing.

Create and configure a multi-dimensional Analysis Services cube in SQL Server Data Tools, connecting to Adventure Works data warehouse, building a data source view, and defining key relationships.

Create and configure an Analysis Services cube by using a data source view, adding a calculated full name column, and selecting measures and dimensions in the cube wizard.

Learn to create and configure an Analysis Services cube, apply a star schema with fact and dimension tables, and process and deploy for user-friendly data browsing.

Deploy and process the analysis services cube, read relational data, and store it in a multi dimensional format; then add attributes to dimensions to give context to measures.

learn how to create and configure an analysis services cube, reprocess and deploy updates, and explore intersections of attributes, measures, and aggregations with colors, demographics, and Excel cube browsing.

Create and configure an Analysis Services cube, connect to external data sources, and browse and deploy Adventure Works Internet Sales cube using pivot tables in Excel with attributes and measures.

Explore how to create and configure multi-dimensional cubes, define measures in a fact table, and use hands-on practice with the Adventure Works DW 2012 database for exam readiness.

Explore the components of a multi dimensional cube through a hands-on demonstration, learn how to create and configure the cube, and practice by building your own multi dimensional cube.

Explore measures and measure groups, dimensions, cube actions, and cube security to build and secure effective cube-based data models.

Explore measures, measure groups, and cube storage, and learn how aggregations from fact tables report metrics. Grasp the default sum, calculated measures, and how formatting and visibility define the cube.

Explore how measures are formatted with numeric, date, and boolean options, including thousand separators and currency signs, plus additive, semi-additive, and non-additive aggregations across product, customer, and time dimensions.

Explore measure groups that organize related measures for fact table, define dimension aggregations, and manage storage and partitions using reference relationships, recursive joins, and many-to-many relationships in analysis services cubes.

Explore measure group storage options in Analysis Services, comparing molap, holap, and rolap; learn how multidimensional storage boosts performance, while rolap stores data relationally with no latency.

Configure partial pre-calculated aggregations to balance performance and memory. Roll up leaf-level calculations to higher levels (week to month to quarter to year) rather than storing every calculation.

Explore dimensions in data models, including storage, hierarchies, and how grouping and sorting organize data for reports.

Explore how dimensions provide context for measures, with dimension tables (entities) and attributes, and learn to rename attributes for friendly user terms, organize into folders, and form hierarchies.

Explore dimension storage options for analysis services, including multidimensional molap and rolap approaches, showing how cube processing populates and stores dimension data for real time versus high performance needs.

Groupings form member groups by discretization into buckets like age ranges or yearly income, with sorting by key or a secondary attribute such as month number for intuitive presentation.

Configure measures, measure groups, and dimensions in the cube, adjust currency formatting with thousand separators for unit price and sales amount, and apply grid view with custom formats.

Create and modify measures in the cube, applying aggregations like distinct product count and counting non-empty values. Deploy and reprocess, then explore partitions and measure groups to scale analysis services.

Edit measures, measure groups, and dimensions in a data model by refining attributes, renaming items, and deploying updates, then reprocess to reflect calendar year, semester, and quarter in the browser.

Add a new measure group to the cube, join facts to dimensions, and configure a dim employee with regular relationships to reseller sales and sales territory to enable multi-fact analysis.

Create a snowflake schema with a new fact table and dimension table, deploy updates, and examine how reference relationships and non one-to-one relationships affect measures in Excel.

Learn to discretize the employee base rate into buckets, configure measures and formatting, deploy and process the cube, refresh data, and customize dimension and sort-by options in analysis services.

Understand measures, their properties and formatting, when to use measure groups and storage types for real-time cube browsing, and how dimensions, attributes, and hierarchies aid navigation.

Explore how measures are created for fact table columns, default aggregations (additive, semi-additive, non additive), and how measure groups and dimensions with hierarchies enable formatting and browsing.

Customize cubes, manage translations, and monitor KPIs and performance indicators while implementing cube security for data models and reports.

Learn to use actions in Analysis Services cubes to drill through data and surface reporting datasets by configuring standard, proprietary, or reporting actions that respond to user clicks and KPIs.

Perspectives act as subset views of the cube in Analysis Services, guiding focused user attention without security control; permissions remain on the cube, not the perspective.

Explore key performance indicators (KPIs) that measure business success by evaluating a value against a goal using MDX expressions and calculated members, with status and optional icons.

Configure translations for a dimension or cube to display labels and captions. Specify the language ID of the application browsing the cube and map table columns to show translations.

Explore Analysis Services security by applying grant or deny permissions to principals, configuring server and database roles in SSMS, and setting cell and dimension permissions inside the cube.

Explore four read permissions in a mobile app database: object read, cell read, read contingent, and read right; learn how write back persists edits within x-statement restricted ranges.

Define and manage dimension permissions at the dimension, attribute, and member levels in Analysis Services, granting group access to dimension members such as 2012 and 2011 with granular control.

Customize cubes and cube security by creating a KPI for cost ratio, defining value, goal, and status expressions, and enabling drill-through actions.

Customize cubes for analysis services by selecting customer demographics and key measures, use MDX drill-through, and create a perspective for a secure, view-only view of fact internet sales.

Navigate external data integration, cube customization, and security in Analysis Services by creating perspectives, translations, and granular MDX permissions to control who sees what.

Create and deploy security roles within your project to ensure roles go with the deployment, then redeploy to apply changes; explore drill-through, KPIs, perspectives, and translations in SSAS multi-dimensional cubes.

Analyze exam scenarios by selecting and configuring actions for business cases and mdx basics, including empty statements. Explore cube security, perspective views, translations, and reporting services action types.

Explore how actions extend analysis services and enable client calls to server-stored code, with MDX-based indicators, perspectives for focused views, translations, and server role, cell and dimension permissions.

Explore an introduction to MDX and understand why you should use MDX, then learn how to write SBX queries.

Introduces MDX, the query language for retrieving multi-dimensional data from an Analysis Services cube. Explore MDX syntax, sets, tuples, and named calculations, and its XMLA-based extensions for Analysis Services.

Explore why to use MDX to query and extend cube functionality, including key performance indicators, calculated members, and name sets, while understanding security granularity and when MDX is essential.

Learn MDX select syntax, including placing measures like sales amount on columns and rows, and understand from and where clauses in a basic MDX query from the Adventure Works cube.

Demonstrate constructing an MDX select statement by specifying columns and rows, using 0 and 1 to assign axes, and selecting the measure sales amount and the dimension customers gender.

See how MDX uses the filter clause to subset data by product color and unit price. Compare MDX filtering with SQL where clauses within an Adventure Works internet sales context.

Explore mdx sets that return members with shared dimensionality, using curly brackets in select statements to combine the gender dimension (male and female) for the sales amount measure.

Mdx powers queries in applications like sql server management studio, excel, and sql server data tools, returning information from a aq; monitor these mdx queries with profiler for analysis services.

Explore MDX multi dimensional expressions by building queries on the Adventure Works sales cube, filtering by year 2006, gender, color, and calendar year sets to analyze sales amount.

Master MDX techniques for data modeling in Analysis Services, including using between and set expressions, creating calculated members for profit, and navigating calendar hierarchies with descendants and children.

Learn to build a calculated member profit using MDX, deriving it from sales amount minus total costs, and explore MDX syntax for selects, sets, and placement on rows and columns.

Master MDX syntax and querying techniques, create calculated members and name sets, and follow demonstrations with management studio and sequel server data tools to extend cubes.

Explore mdx language to query multi-dimensional cubes and extend Analysis Services with key performance indicators, named calculations, calculated members, and named sets, and learn the components of mdx statements.

Explore the tabular data model concept and learn to build tabular models with Power Pivot and SQL Server Data Tools.

Explore tabular data models and power pivot basics, including working with power pivot, length and non linked tables, and the features available within power pivot.

Explore how tabular data models use an in-memory relational database powered by x velocity, created with power pivot and deployed to analysis services for Excel or Power View on SharePoint.

Build in-memory tabular data models with daks in PowerPivot for Excel, importing tables from multiple sources and maintaining relationships.

Explore linked tables in Power Pivot and Excel, where updates to the worksheet automatically refresh the pivot table, versus non-linked ranges that do not persist changes back to Power Pivot.

Explore server based tabular data models, create them with sql server data tools, and secure them using analysis services in tabular mode with the x velocity engine for server-side processing.

Create a tabular data model with SQL Server Data Tools. Restore a Power Pivot model to an Analysis Services tabular instance and deploy to development, test, and production servers.

Sql server data tools creates a workspace database in memory for a tabular data project. Workspace uses the project name with an underscore and can be kept, stored, or removed.

Explore tabular data models in Analysis Services and how they compare to Power Pivot, including perspectives, partitions, processing options, in-memory storage with x velocity, and direct query mode.

Create roles and assign Active Directory users or groups to a tabular data model. Use DAX to define role-level security and set permissions like read, refresh, or administrator.

Demonstrates tabular data models with Power Pivot in Excel 2010, importing data from SQL Server Adventure Works DW 2012, establishing relationships and building hierarchies in diagram view.

Explore tabular data modeling with power pivot, creating pivot tables and charts, automatic data type detection, linked and unlinked tables, and integrating external data sources like SQL Server.

Create and relate tabular data models by importing external sources into Power Pivot, defining relationships such as product key, and deploying a server-based Analysis Services tabular project.

Import tables from a SQL Server data source, select related tables for Adventure Works 2012, and explore in-memory versus direct query options, relationships and partitions before deploying AW tabular database.

Manage tabular data in Analysis Services by deploying and refreshing workspace databases, restoring power pivot projects to tabular models, and browsing Excel pivot tables with color slicers.

Explore tabular data modeling with Power Pivot, add color-based slicers, and browse models across Excel and SQL Server Analysis Services.

Explore exam scenarios for Power Pivot and tabular data models, covering project creation, relationships, pivot tables and charts, security, deployment, and Excel integration.

Study tabular data models and their implementations via Power Pivot and Analysis Services in tabular mode, enabling in-memory data with pivot tables and charts. Explore deployment and direct query options.

Explore data analysis expressions (DAX) and learn how to work with DAX, starting with an introduction, in Microsoft 70-466: implementing data models and reports.

Explore an introduction to DAX, learning to use DAX functions, run DAX queries, and create calculated columns and measures for data modeling and reporting.

Explore DAX, a formula-based language for Power Pivot that handles relational data and offers more advanced functionality than Excel; reference complete tables or columns, with filters to narrow results.

Master DAX syntax, where formulas begin with = and reference columns or tables, using operators, constants, and function results. Learn measure names in brackets and fully qualified vs non-qualified names.

Explore DAX built-in functions categorized into date and time, information, logical, math and trigonometry, statistical, and text, and see how DAX extends beyond Excel in these areas.

Discover how DAX queries access tabular data models, written manually in editors, and how Excel can use them to filter, order, and summarize results.

Create calculated columns in Power Pivot data window by supplying a column name and a valid DAX expression to produce a calculated value, similar to calculated members in multidimensional cubes.

Explore DAX formulas that encode sophisticated business logic, used in pivot tables and pivot charts, and evaluated at every intersection in the pivot cell.

DAX expressions enable multiple table relationships, but only one can be active at a time; use the relationship function to specify the active link in a measure or calculated column.

Explore DAX in Power Pivot to create calculated columns and measures, compute total quantity, ratio of cost to sales, and average sales, using IntelliSense and pivot tables.

Prepare for the exam by gaining hands on experience with Power Pivot, creating calculated columns and measures, and using DAX functions; know when to use a calculated column versus measure.

Explore DAX, the data analysis expressions language for Power Pivot, to create calculated columns and measures in Power Pivot and tabular data projects, with syntax similar to Excel.