• Build a Tabular Data Model (17%)
  • Configure permissions and roles in Business Intelligence Semantic Model (BISM).
    • This objective may include but is not limited to: server roles; SSAS database roles; implement dynamic security (custom security approaches); role-based access; Test security permissions; cell level permissions
  • Implement a tabular data model.
    • This objective may include but is not limited to: define tables; import data; calculated columns; relationships; hierarchies and perspectives; manage visibility of columns and tables; optimize BISM for Crescent; mark a date table; sort a column by another column
  • Implement business logic in a tabular data model.
    • This objective may include but is not limited to: measures and KPIs; DAX; relationship navigation; time intelligence; context modification
  • Implement data access for a tabular data model.
    • This objective may include but is not limited to: manage partitions; processing; Vertipaq versus Direct Query
• Manage, Maintain, and Troubleshoot an SSAS Database (18%)
  • Analyze data model performance.
    • This objective may include but is not limited to: performance consequences of DWH design; optimize performance by changing the design of the cube or dimension; Analyze and optimize performances of an MDX/DAX query; optimize queries for huge data sets; optimize MDX in the calculations; performance monitor counters; DMVs; performance counters (new for tabular model), growth of the cache, logging options
  • Process data models.
    • This objective may include but is not limited to: processing tables or partitions for tabular models; processing databases, cubes, dimensions for multidimensional models; full processing versus incremental processing, remote processing; lazy aggregations; automate with Analysis Management Objects (AMO) or XML for Analysis (XMLA)
  • Troubleshoot data analysis issues.
    • This objective may include but is not limited to: use SQL Profiler; troubleshoot duplicate key dimension processing errors; error logs and event viewer logs of SSAS, mismatch of data: incorrect relationships or aggregations; dynamic security issues; validate logic and calculations
  • Deploy SSAS databases.
    • This objective may include but is not limited to: Deployment Wizard; BIDS; SSMS; automation; test solution post deployment; deciding whether or not to process
  • Install and maintain a SSAS instance.
    • This objective may include but is not limited to: software installation of SSAS; development tools, development and production box installation considerations; upgrade; data file and program file location, planning for Administrator accounts; Updates (service packs); install and maintain each instance type of Analysis Services, including PowerPivot; restore and import PowerPivot.
• Build an Analysis Services Database (38%)
  • Design dimensions and measures.
    • This objective may include but is not limited to: given a requirement, identify the dimension/measure group relationship that should be selected; design patterns for representing business facts and dimensions (many-to-many relationships); design dimensions to support multiple related measure groups (many related fact tables); handle degenerate dimensions in a cube; identify the attributes for dimensions; identify the measures; aggregation behavior for the measures; hierarchies
  • Implement and configure dimensions in a cube.
    • This objective may include but is not limited to: translations; attribute relations; hierarchies; implement SQL Server Analysis Services (SSAS) dimensions and cubes; identify the Attribute Relationships that should be made for a given set of Attributes in a dimension; develop new custom attributes on dimensions; detect possible design flaws in attribute relationships; create attribute relationships correctly in an analysis services dimension; implement time Dimensions in cubes; manage SSAS parent-child dimensions; dimension type
  • Design a schema to support cube architecture.
    • This objective may include but is not limited to: multidimensional modeling starting from a star schema; relational modeling for a Data Mart; choose or create a topology; identify the appropriate data types with correct precision and size
  • Create measures.
    • This objective may include but is not limited to: logically group measures; select appropriate aggregation functions; format measures
  • Implement a cube.
    • This objective may include but is not limited to: use Business Intelligence Development Studio (BIDS) to build the cube; use BIDS to do non additive or semi additive measures in a cube; measures, perspectives; translations; dimension usage; cube specific dimension properties; measure groups; implement reference dimensions; implement many to many relationships; implement fact relationships; implement role-playing relationships; define granularity; create and manage linked measure groups and linked dimensions; actions
  • Create Multidimensional Expressions (MDX) queries.
    • This objective may include but is not limited to: MDX authoring; identify the structures of MDX and the common functions (tuples, sets, topcount, SCOPE, etc.); identify which MDX statement would return the required result; implement a custom MDX or logical solution for a pre-prepared case task; graphical query designer or the generic query designer
  • Implement custom logic in a data model.
    • Must include: Data Analysis Expressions (DAX) calculated columns and measures. This objective may include but is not limited to: key performance indicators (KPI); calculated members; use MDX functions to calculate members; relative Measures (growth, YoY, same period last year), % of total using MDX; named sets; adding intelligence to dimensions; Analysis Services stored procedures
  • Implement storage design in a multidimensional model.
    • This objective may include but is not limited to: aggregations; partitions; storage modes; proactive caching; manage write-back partitions
  • Select an appropriate model for data analysis.
    • This objective may include but is not limited to: UDM; Scalability, Cleansed; traditional hierarchical; high volume of data; advanced features (support for financial applications; many to many); organizational BI; Tabular Data Model: raw data; relational tables and relationships; simpler data structures; Team and personal BI; choose between multidimensional and tabular models
• Build a Report with SQL Server Reporting Services (SSRS) (28%)
  • Design a report.
    • This objective may include but is not limited to: selecting report components (crosstab report, Tablix, design chart, data visualization components), report templates (Report Definition Language), identify the data source and parameters; designing a grouping structure; drill-down reports, drill-through reports; determine if any expressions are required to display data that is not coming directly from the data source
  • Implement a report layout.
    • This objective may include but is not limited to: formatting; apply conditional formatting; page configuration; headers and footers; matrix; table; chart; image; list; indicators, maps, grouping; use Report Builder to implement a report layout; creating a range of reports using different data regions; custom fields (implementing different parts of the report); collections (global collections); using expressions; data visualization components; identifying report parts; group variables and report variables
  • Configure authentication and authorization for a reporting solution.
    • This objective may include but is not limited to: configure server-level and item-level role-based security; configure Windows authentication and custom authentication (forms-based authentication); configure Reporting service security (setup or addition of role) ; authenticating against data source; storing credential information; describe Report Server security architecture and site level security; create system level roles; item level security; create a new role assignment; assign Windows users to roles; secure reports using roles; configure SharePoint groups and permissions
  • Implement interactivity in a report.
    • This objective may include but is not limited to: drilldown; drillthrough; interactive sorting; parameters: (databound parameters; multi-value parameters); create dynamic reports in SSRS using parameters; show/hide property; actions (jump to report); filters; parameter list; fixed headers; document map, embedded HTML
  • Troubleshoot reporting services issues.
    • This objective may include but is not limited to: querying the executionlog views in ReportServer database; viewing reportingservices log files; Windows Reliability and Performance monitor ; Using the ReportServer: Service and Web Service objects; long running reports; rendering; connectivity issues, use SQL Profiler; data reconciliation: incorrect relationships or aggregations; dynamic security issues; validate logic and calculations
  • Manage a report environment.
    • This objective may include but is not limited to: manage subscriptions and subscription settings, manage data sources, integrating SharePoint Server 2010; email delivery settings; managing the number of snapshots; manage schedules, manage running jobs, manage report server logs; manage report server databases, manage the encryption keys, setting up the execution log reporting; reviewing the reports; site level settings; design report lifecycle; automate management of reporting services; create a report organization structure; install and configure reporting services
  • Configure report data sources and datasets.
    • This objective may include but is not limited to: query types (stored procedure versus table versus text only); parameterized connection strings (dynamic connection strings); filter location (dataset vs. query); ; configure data source options i.e. extract and connect to different LOB platforms; shared and embedded data sources and datasets; connect to SQL Azure database; SQL Data Market; MDX queries; work with non-relational data sources such as xml or SharePoint

B. Designing business Intelligence Solutions with MS SQL 2012 467

• Plan Business Intelligence (BI) Infrastructure (15%)
  • Plan for performance.
    • This objective may include but is not limited to: optimize batch procedures: extract, transform, load (ETL) in SQL Server Integration Services (SSIS)/SQL and processing phase in Analysis Services; configure Proactive Caching within SQL Server Analysis Services (SSAS) for different scenarios; understand performance consequences of Unified Dimension Model (UDM) and Data warehouse (DWH) design; analyze and optimize performances of Multidimensional Expression (MDX) and Data Analysis Expression (DAX) queries; optimize queries for huge data sets; understand the difference between partitioning for load performance versus query performance in SSAS; appropriately index a fact table; optimize Analysis Services cubes in UDM; create aggregations using Usaged Based Optimizations
  • Plan for scalability.
    • This objective may include but is not limited to: Multidimensional OLAP (MOLAP); Relational OLAP (ROLAP); Hybrid OLAP (HOLAP)
  • Plan and manage upgrades.
    • This objective may include but is not limited to: plan change management for a BI solution
  • Maintain server health.
    • This objective may include but is not limited to: design an automation strategy
• Design BI Infrastructure (16%)
  • Design a security strategy.
    • This objective may include but is not limited to: configure security and impersonation between database, analysis services and frontend; implement Dynamic Dimension Security within a cube; configure security for an Extranet environment; configure Kerberos Security; skills on authentication mechanisms, ability to build secure solutions end to end; design security roles for calculated measures; understand the tradeoffs between regular SSAS security and dynamic security; plan and implement security requirements of a BI solution
  • Design a SQL partitioning strategy.
    • This objective may include but is not limited to: choose the proper partitioning strategy for the data warehouse and cube; implement a parallel load to fact tables by using partition switching; use data compression in Fact tables
  • Design a backup strategy.
    • This objective may include but is not limited to: design a High Availability (HA) and disaster recovery (DR) strategy; proactively preventing issues
  • Design a logging and auditing strategy.
    • This objective may include but is not limited to: design a new SSIS logging infrastructure (i.e. info available thru the catalog views); validate data is balancing and reconciling correctly
• Design a Reporting Solution (24%)
  • Design a Reporting Services dataset.
    • This objective may include but is not limited to: data query parameters; creating appropriate SQL queries for an application (MDX queries); managing data rights and security; extracting data from Analysis Services; balancing query-based processing versus filter-based processing; managing data sets through the use of stored procedures
  • Manage Excel Services/Reporting for SharePoint.
    • This objective may include but is not limited to: configure data refresh schedules for PowerPivot published to SharePoint; publish BI info to SharePoint; use SharePoint to accomplish BI administrative tasks
  • Design a data acquisition strategy.
    • This objective may include but is not limited to: identify the data sources that needs to be used to pull in the data; determine the changes (incremental data) in the data source (time window); identify the relationship and dependencies between the data sources; determine who can access which data; what data can be retained for how long (regulatory compliance, data archiving, aging); design a data movement strategy; profile source data
  • Plan and manage reporting services configuration.
    • This objective may include but is not limited to: native mode
  • Design BI reporting solution architecture.
    • This objective may include but is not limited to: linked reports, drill-down reports, drill-through reports, migration strategies, access report services API, sub reports, Code-Behind strategies; identify when to use Reporting Services, Report Builder, or Crescent; design/implement context transfer when interlinking all types of reports (RS, RB, Crescent, Excel, PowerPivot); implement BI tools for reporting in SharePoint (Excel Services versus Performance Point versus Reporting Services); select a subscription strategy
• Design BI Data Models (34%)
  • Design the data warehouse.
    • This objective may include but is not limited to: design a data model that is optimized for reporting; design & build a cube on top; design enterprise data warehouse (EDW) and OLAP cubes; choose between natural keys and surrogate keys when designing the data warehouse; use the facilities available in SQL Server to design, implement and maintain a data warehouse (partitioning, slowly changing dimensions (SCD), change data capture (CDC), Clustered Index Views etc.); identify design best practices; implement a many-to-many relationship in an OLAP cube; design a data mart/warehouse in reverse from an Analysis Services cube (or empty Analysis Services cube that was created referring requirements); use rowstamp in the data warehouse; choose between performing aggregation operations in the SSIS pipeline or the relational engine; select surround architecture
  • Design a schema.
    • This objective may include but is not limited to: multidimensional modeling starting from a star schema; relational modeling for a Data Mart; choose or create a topology
  • Design cube architecture.
    • This objective may include but is not limited to: produce efficient aggregated cubes; partition cubes and build aggregation strategies for the separate partitions; design a data model; choose the proper partitioning strategy for the data warehouse and cube; design the data file layout for a data warehouse keeping maximum performance in mind; given a requirement, identify the Aggregation method that should be selected for a measure in a MOLAP cube; design cube aggregations to maintain a balance between storage and performance; performance tune a MOLAP cube using aggregations; design a data source view; cube drill-through and write back actions
  • Design fact tables.
    • This objective may include but is not limited to: design a data warehouse that supports many to many dimensions with factless fact tables
  • Design BI Semantic Models.
    • This objective may include but is not limited to: plan for a multidimensional cube; write a UDM model with many to many (this is related to MDX/BISM code, but it is a good example for exercises; choose between UDM and BISM depending on the type of data and workload
  • Design and create MDX calculations.
    • This objective may include but is not limited to: MDX authoring; identify the structures of MDX and the common functions (tuples, sets, topcount, SCOPE etc.); identify which MDX statement would return the required result (single result and multiple MDX options provided to test taker); implement a custom MDX or logical solution for a pre-prepared case task
• Design an ETL Solution (11%)
  • Design SSIS package execution.
    • This objective may include but is not limited to: using new project deployment model; passing values at execution time; share parameters between packages
  • Plan to deploy SSIS solutions.
    • This objective may include but is not limited to: deploy the package to another server with different security requirements; secure Integration Services packages that are deployed at the file system; demonstrate awareness of SSIS packages/projects and how they interact with environments; decide between performing aggregation operations in the SSIS pipeline or the relational engine
  • Design package configurations for SSIS packages.
    • This objective may include but is not limited to: avoid repeating Configuration Information entered in SSIS packages and use configuration files