Microsoft 70-467 Designing Intelligence Solutions w/SQL 2012

Compare OLTP and OLAP databases, define a data warehouse, explore its architecture, and walk through the process of creating a data warehouse.

Compare OLTP and OLAP by exploring data warehousing architecture, data presentation options, and the trade-offs between normalized, transaction-optimized OLTP and denormalized, batch-processed OLAP for reporting.

Explore how a data warehouse, an olap relational database, supports reporting, trending, and forecasting with incremental loading and historic data for proactive analysis and data mining.

Explore centralized data warehousing, data marts, and hub-and-spoke designs, where OLAP databases serve districts and maintenance cascades from the centralized warehouse to each data mart.

Identify key measures: total sales, cost, and profit; store them in a fact table. Slice by year, department, and product from data sources; plan transformations for consistency, aggregations, and lookups.

Learn how ETL builds a data warehouse by transforming, validating, and loading source data, and why SSIS is the primary ETL tool, including version history and migration guidance.

Learn to present OLAP data from a data warehouse using SQL Server reporting services and analysis services cubes, with Excel front ends and Power Pivot and Power View in SharePoint.

Contrast oltp and olap: oltp uses normalized tables for transactions; olap uses denormalized fact and dimension tables for efficient querying.

Develop exam-ready skills by modeling data for OLAP databases, comparing hub-and-spoke centralized warehouses, data marts, and OLAP architectures; create tabular and multidimensional projects, reports, and Excel workbooks.

Compare OLTP and OLAP databases, show normalization for queries versus denormalization for performance, outline data warehouse architectures—centralized marts and hub-and-spoke—and define metrics, views, and presentation with Excel services or SharePoint.

Explore data modeling and implementing a data model to support intelligence solutions in this module.

Identify reporting metrics and areas such as total sales and terminated accounts. Show how measures in fact tables are analyzed by dimensions in dimensional modeling with time dimensions.

Compare star and snowflake schemas to design data warehouses; star schema directly joins fact to dimension tables for performance, while snowflake resembles normalization with subdimensions and hierarchies.

Learn how a time dimension enables evaluating metrics across time, including fiscal year, quarters, holidays, and temporal hierarchies, and how to create a dedicated time dimension table for SQL queries.

Align data modeling with the lowest time granularity, choose surrogate keys for centralized data warehouse, and classify measures as additive, non-additive, or semi-additive, while reusing time dimensions across fact tables.

Implement a data model by aligning logical and physical designs to business needs, selecting star or snowflake schemas, and securing stakeholder buy-in to optimize granularity and return on investment.

Define the logical model with dimension tables and fact tables, attributes, surrogate and business keys, data types, then define measures and classify them as additive, non additive, or semi additive.

Define the physical model by partitioning disks and file groups, align indexing and compression strategies with business needs, and assess options like parallel data warehouse or fast track configurations.

examine fact and dimension tables in a data warehouse, join the fact internet sales to product and customer dimensions to expose metrics like sales amount, quantity, and product attributes.

Explore exam scenarios on data warehouse modeling, distinguishing star versus snowflake schemas, identifying fact and dimension roles, time dimensions, and surrogate versus business keys in dimension tables.

This module reviews data modeling, compares star and snowflake schemas, explains dimension and fact tables, time dimensions, and logical and physical implementations, including storage and server resources.

Explore an introduction to SQL Server Integration Services and examine SSIS tools, data flow, and control flow.

Explore SQL Server Integration Services and the tools to create SSIS packages. Understand how SSIS replaced Data Transformation Services in SQL Server 2005 and the DTS deprecation by 2012.

Upgrade older ssis packages to 2012 via the executable or command line, or by importing into a 2012 integration services instance; dtl packages and active x controls are not upgraded.

Explore SSIS tools, starting with the import export wizard for simple one-source, one-destination packages with limited transformations, and extend with sequel's server data tools SSD to build more robust packages.

Explore data flow tasks as core control flow elements that encapsulate data from source through transformations to destination, with memory buffers and connection managers enabling scoped reuse.

Identify data source categories and where data is extracted or entered, including database sources, file sources such as flat file and Excel, and custom sources.

Explore blocking, semi-blocking, and nonblocking transformations in data flows, and learn how derived columns, sorts, merges, and custom script and .NET components provide consistent, pre-aggregated data.

Improve SSIS package performance by avoiding full blocking transformations, sorting in the query, and selecting only needed rows and columns, using development resources and delaying validation when deploying.

Understand control flow in SSIS, including variables, expressions, and task precedents, and how control flow tasks are executables within a package; data flow remains encapsulated, accessible via parameters or variables.

Explore user defined versus system defined variables within Sosias, their scope on tasks and packages, and data types; contrast parameters and variables, including sensitivity and required options.

Explore how the integration services expression language drives control flow by dynamically setting values and properties, using variables, parameters, and operator predicates to handle success, failure, or completion.

leverage precedence in control flow tasks to route package execution by success or failure, using variables to branch to multiple success or failure tasks and define data flow destinations.

Explore integration services control flow containers, including for each, for loop, sequence, and group containers, to group tasks by a specific execution context.

Use for each and for loop containers to iterate over collections such as folders or files, configuring data flow tasks with pathname, and manage count through initialization, evaluation, and iteration.

Learn how sequence containers organize tasks in SQL Server Integration Services, enabling transactional scopes to roll back multiple data flow tasks together via MSDTC, while considering overhead.

Explore how SSIS transactions group multiple data flow tasks into a single unit of work, enabling rollback on failure and comparing MSA DTC with native begin tran, commit, and rollback.

Enable checkpoints to record package execution and failures, save an XML checkpoint file, and restart from the failure point to reduce rerun time and prevent duplication in the data warehouse.

Explore building SSIS packages with control flow and data flow tasks, create a full name, and use a foreach loop with a conditional split to separate managers from employees.

Debug and execute SSIS packages, observe data flow behavior, and track row counts and variables during multi-file processing, while handling failures by dropping and recreating tables in Adventure Works 2012.

Explore how ssis packages manage control flow with sequence containers, transaction handling, and error event handlers. Learn to configure connections, group objects, and debug with breakpoints.

Gain exam-focused coverage of data sources, data flow and control flow tasks, transformations (synchronous and asynchronous), data destinations, and parameter and variable usage, including conditional split routing.

Review how SQL Server Integration Services distinguish control flow from data flow and move data from source to destination through transformations, using variables, parameters, and transactions to coordinate execution.

Deploy integration services packages and projects and secure them throughout the deployment workflow for robust, compliant solutions.

Explore deployment options for SQL Server Integration Services, including deploying packages to MSDB or the file system, importing packages, and deploying projects with project-wide parameters to pass values between packages.

Deploy packages with SSIS by placing them in MSDE file system or importing from an SSIS instance or X file; deployment manifests exist, but project parameter usage is not supported.

Create the ssis catalog before deploying to Integration Services; the catalog stores projects and their parameters, and you must convert to the new deployment model for SQL Server 2005–2008.

Explore package protection levels and encryption options, including encrypt sensitive with user or password, and encrypt all with user or password, with parameters as the only sensitive items.

Deploy and secure integration services packages with the deployment wizard, specifying destination server and parameters, then import a single package and configure encryption for package security.

Explore exam scenarios on SSIS 2012 project deployment, project parameters, and securing packages, including upgrade paths from 2005–2008 and using the integration services catalog.

Explore deploying and securing integration services packages and projects, compare project deployment with package deployment, and use cross‑package parameters while deploying to MSDE, file system, or file store with encryption.

Explore SQL Server Reporting Services and learn how reports are created. Examine the components that make up a Reporting Services report.

Explore the fundamentals of Reporting Services, including report creation and delivery, and learn to design different reports in Reporting Services.

Trace the evolution of reporting services from SQL Server 2000 to 2005 and express with Advanced Services; compare native vs SharePoint installations and export formats (Excel, Word, XML, CSV, PDF).

Create SSRS reports using SQL Server Data Tools Report Builder or Power View, then deploy them to Report Manager or SharePoint based on native mode or SharePoint integrated mode.

Design ssrs reports with the report definition language, an xml document, configuring data sources and datasets, then drag components into data regions like tables, lists, or matrices.

Learn how grouping expressions organize data in reporting services, including row and column grouping for tables, matrices, and lists, with drill down, drill through, and expressions to create aggregations.

Explore the Reporting Services expression language to perform aggregations, type conversions, comparisons, and concatenations, and apply dynamic formatting such as conditional background color based on value ranges.

Learn how to create drill down reports by toggling visibility to reveal country, state, and city details, with full data rendered but no query performance gain.

Learn how reporting services use parameters to filter reports and distinguish report filters from dataset parameters, and configure prompts, data types, and allowable values.

Drillthrough lets you open a separate report with a different dataset, passing parameters from the calling report to reveal detailed information while reducing overall results.

Discover visual components in reporting services, including charts and chart styles, images embedded or sourced from files or databases, and data bars, sparklines, indicators, and gauges for dashboards.

Create a report in reporting services using SQL Server data tools, linking a shared data source to Adventure Works data warehouse 2012 and building a tabular, drill-down report.

Create a tabular report in reporting services by using a shared data source, building a sales dataset, and applying grouping by category and subcategory, with currency formatting.

Apply dynamic formatting and conditional formatting in reports to show sales as currency, color red when under $100, and black otherwise, with category-based drill-down visibility.

Configure a dataset parameter with values from a query, embed a dataset, and set a default like bikes to render the report in SQL Server Data Tools.

Review SQL Server Reporting Services concepts, including report components, renderings, grouping with data components, drill through and drill down, and report and data set parameters.

Explore reporting services installations, including native mode and SharePoint integrated, and learn to create ad hoc reports with report builder and sql server data tools, including data sources and charts.

Learn how to deploy SSRS reports, manage report execution and security, configure subscriptions, and set up data alerts to automate reporting in SQL Server 2012.

Deploy and secure reports by comparing native mode and SharePoint integrated mode, deploying from SQL Server Data Tools to a library or report manager, via report manager or SharePoint.

Configure report security with integrated Windows authentication and SharePoint permissions, using inheritance and role-based access, while managing report server config, data source credentials, and Kerberos for double-hop scenarios.

Explore report execution, including caching, snapshots, subscriptions, and data alerts. Learn on-demand rendering, pull versus push execution, and how subscriptions deliver emailed reports.

Explain how report caching reduces rendering time by storing an intermediate report in the report server temp database, using RDL and dataset flow, with a 30-minute flush.

Learn how report snapshots run on a daily schedule, storing the full intermediate report in the report server database, and how dataset and filter parameters affect data retrieval.

Explore how subscriptions deliver reports by email or to a fileshare on a schedule, including parameter values and delivery options; differentiate generic and data driven subscriptions and responsibilities for maintenance.

Data alerts provide data-driven notifications for SharePoint integrated reports by defining data feeds, rules, schedules, and email settings, with hourly checks for thresholds such as total sales.

Deploy reports and secure data sources in SQL Server Data Tools, manage data sources and report security, and configure caching and snapshots for the customer sales report.

Explore how to deploy reporting snapshots and manage history versions. Configure object-level security in report manager, including inheritance, role assignments, and permission reversion.

Master reports security, the report execution process, and the difference between intermediate and rendered reports. Compare cached reports and report snapshots, learn data alerts, formats, and SharePoint integrated mode implications.

Explore deployment methods for reporting services, security with inherited and object level permissions, execution, caching, and report snapshots that support history, performance, subscriptions, and data alerts via SharePoint integration.

Explore the fundamentals of multidimensional cubes, examine the components of a multidimensional cube, and learn how to customize cubes.

Explore the basics of cubes and the components of multi-dimensional cubes, then watch a demonstration on how to create and customize cubes.

Explore why oltp databases optimize transactions and fall short for reporting, and how olap cubes offer pre-aggregations, dimensions and facts, enabling faster querying and browsing.

Define the data source and data source view to feed the cube, then configure measures and dimensions tied to fact tables and attributes to quantify multi-dimensional data.

Explore measures in a cube, including storage and aggregations, default sum, numeric and non-numeric measures, and deriving measures from other measures with attention to name, formatting, and visibility.

Learn how measure formats control display, including numeric, date/time, boolean, and user-defined formats, and compare additive, semi-additive, and non-additive measures with examples.

Measure groups define how measures are aggregated by dimensions and stored for cube browsing, user table; understand relationships between fact and dimension tables, including regular, reference (snowflake), and many-to-many joins.

Compare molap, the hybrid online analytical processing approach, and relational storage for multi-dimensional cubes, highlighting performance, latency, data freshness, and the balance of relational and multidimensional data.

Learn how aggregations provide pre-calculated summaries of leaf-level data, why we configure them selectively, and how daily aggregations roll up to week, month, quarter, semester, or year to accelerate queries.

Explore dimensions, their storage hierarchies, and grouping and sorting. Rename attributes to friendly terms and organize entities into folders and hierarchies to ease cube browsing.

Compare multidimensional cube storage, the default and best performing option in Analysis Services, with relational storage that provides real-time data but slower processing for large dimensions.

Explore how attribute hierarchies organize dimension members into balanced and unbalanced structures, using the parent attribute to define self-referencing relationships in dimensions such as country, state, and city.

Discretize by grouping consecutive dimension members into equal-value buckets, such as 0-5 or 6-10 age ranges, and adjust sorting from key to alphabetical last names or month numbers.

Explore actions in analysis services, including key performance indicators, translations, and reporting actions to browse Reporting Services reports and drill into specific measures.

Create cube perspectives to tailor what each business unit sees. A sales perspective shows customers, products, time, total sales, quantity, and total cost, with permissions on the underlying cube.

Explore how key performance indicators use MDX expressions and calculated members to gauge business success, define values and goals, and display green, yellow, or red performance with trend data.

Configure translations for both the mentions and the cube to display labels in another language using a translations table and a language ID.

Explore analysis services security including cell and dimension permissions, grant and deny, and role management for multidimensional cubes via sql server data tools or management studio, mindful of redeploy risk.

Configure cube cell level permissions for read, read contingent, and read write, using MDX to restrict users and cell ranges; support write back when the cube is used in Excel.

Dimension permissions define access to dimension data and restrict visibility to individual members, such as granting only the human resources group access to the human resources member, illustrating security granularity.

Create and configure a multidimensional cube in SQL Server Analysis Services using a data source, data source view, and cube wizard; define measures, dimensions, and attributes.

Process, deploy, and browse a multidimensional cube in analysis services, using Excel pivots to explore measures like sales amount, unit price, and discounts by gender and marital status.

Configure currency formatting with thousand separators for internet sales data and set drill-through action named customer demographics on the fact internet sales measure group to return gender, education, and occupation.

Configure and analyze a multidimensional analysis services cube by adjusting partitions and aggregations, creating a sales perspective, and presenting results in Excel.

Master the components of a multi dimensional cube, including measures, measure groups, and dimension tables, and apply storage types, actions, KPIs, and MDX translations.

Examine multi dimensional cubes, their data source and view, dimensions and facts, measures derived from fact tables, MDX-generated measures, perspectives, translations, and KPIs to enable focused analysis.

Explore an introduction to tabular data models, including Power Pivot, and learn to create tabular data models with SQL Server Data Tools.

Explore tabular data models, an in-memory relational model powered by X velocity. Create them with Power Pivot or SQL Server Data Tools, linked tables, deploy to Analysis Services.

Harness power pivot for fast, in-memory data analysis in Excel 2010 as a free add-in with its own data analysis expressions language, and deploy on SharePoint enterprise for server-side processing.

Explore power pivot as an Excel 2010 add-on to import tables in memory, with automatic relation detection and support for multiple data sources and connections.

Understand how linked tables in power pivot auto update from an excel worksheet, while non linked tables do not update and require pasting as a non linked table.

Explore power pivot features, including hierarchies and creating pivot tables and charts from your data. Add slicers that connect automatically to pivots, enabling dynamic filtering as users interact.

Explore server based tabular data models and learn to create a tabular data model using sequel server data tools. Examine the workspace database, the tabular data model, and its security.

Deploy a tabular data model to the server in tabular mode. Leverage the x velocity in-memory engine to connect Power View and Excel, offering more features than Power Pivot.

Create tabular data models in SQL Server Data Tools using a tabular project or a PowerPivot import, then restore PowerPivot projects to an Analysis Services instance in tabular mode.

Explore tabular data models with server data tools, compare to Power Pivot, and leverage debugging features for deployment across development, test, and production servers.

Automatically create a workspace database for a tabular data model in sequel server data tools, and configure whether it stays in memory or is stored on disk on project close.

Explore the tabular data model with perspectives and partitions, optimize performance by separating frequently changing data, and choose between in memory querying with x velocity and direct query mode.

Select Active Directory users or groups to apply tabular data security modes, enforce database level permissions with implicit deny by default, and apply granular row-level security using data analysis expression.

Create a tabular data model in Power Pivot by importing Adventure Works 2012 data, selecting related tables, defining relationships, and building a year hierarchy with calendar year and semester.

Create a tabular data model in Excel with Power Pivot and pivot tables, link related tables, refresh data when sources change, and extend tabular modeling in Visual Studio.

Create a server-side tabular data model in Analysis Services, compare in-memory and direct query modes, define workspace database, relationships, hierarchies, partitions, and perspectives from Power Pivot.

Explore creating a tabular data model with Power Pivot, deploy assets to a workspace database, and access it via Analysis Services tabular, refreshing connections and deploying to the server.

Explore power pivot basics, create power pivot projects, and work with pivot tables while comparing power pivot to tabular data models in Analysis Services, including deployment, configuration, and perspectives.

Explore constructing tabular data models using Power Pivot and SQL Server Data Tools, deploy to Analysis Services in tabular mode, and manage perspectives, partitions, and workspace database.

Explore MDX multi-dimensional expressions and learn why to use them, including writing MDX queries. Introduce DAX for Power Pivot or tabular data projects and show how to use DAX.

Explore an introduction to MDX, a query language for retrieving and creating multi-dimensional objects in Analysis Services, including basic syntax, sets, tuples, and name calculations, not a Microsoft proprietary standard.

Discover why MDX is essential for querying a cube and extending analysis services, including creating key performance indicators, calculated measures, and named sets, and enabling granular security with user-selected members.

Master mdx syntax by crafting a select with sales amount on 0 and customer gender on 1, and filter with where for calendar year 2006 and unit price > 100.

Learn how sets with shared dimensionality use curly brackets to show gender members (male and female) on rows, and how to rewrite queries to display all members.

Explore where MDX is used in Analysis Services tools like Management Studio, Excel, and SQL Server Data Tools, and learn to write MDX for KPI creation and granular security.

Explore data analysis expressions (DAX) basics, including DAX functions, DAX queries, calculated columns, and measures.

Learn DAX, a formula-based language for Power Pivot that extends Excel formulas to relational data, offering functionality beyond Excel functions and differing in references to cells, arrays, and tables.

Learn how to write DAX expressions in Power Pivot for Excel, using the equal sign, constants, and references to columns or tables, and how measure and table names are formatted.

Explore daks functions across categories such as date and time, logical, math, statistical, and text, and understand function overloading with multiple argument types; practice with TechNet labs offline.

Explore dax queries that retrieve data from a tabular data model and discover tools, including a Visual Studio 2010 extension, that provide intellisense for dax functions, measures, filtering, and summarizing.

Create calculated columns in Power Pivot with a DAX formula, defined in the data view for all imported tables, using a name and a valid expression—similar to calculated members.

Explore how DAX measures capture sophisticated business logic, created with DAX, and used in pivot tables and pivot charts for Power Pivot projects.

Explore how Power Pivot projects support multiple table relationships, and define the active relationship (one at a time) using the relationship function in DAX queries.

Master exam scenarios for designing intelligence solutions with MDX and DAX, including cube queries, calculated members, and named sets, with hands-on experience and syntax across DAX functions.

Master writing MDX queries and DAX expressions to query cubes, define sets, and create KPIs, calculated columns, and measures in power pivot and tabular models.