Preparation for the 70-467 SQL BI exam

Microsoft does not provide any preparation materials for the 70-467 exam [Designing Business Intelligence Solutions with Microsoft SQL Server]. There are Microsoft Virtual Academy videos here or here, which are nice to watch but definitelly not enough to be able to answer any particular question. Microsoft does though provide a list of topics here 70-467, so, while preparing I was collecting related links.

Plan business intelligence (BI) infrastructure (15–20%)
Plan for performance
• Optimize batch procedures: extract, transform, load (ETL) in SSIS/SQL; Link
• Optimize processing phase in Analysis Services, Partitions, Lazy Aggregation; Link, Link1, Link2, Link3
• Configure Proactive Caching within SQL Server Analysis Services (SSAS) for different scenarios; Link, Link1
• Understand performance consequences of named queries in a data source view; Link
• SSAS Performance Counters; Link, Link1
• Analyze and optimize performance, including Multidimensional Expression (MDX) and Data Analysis Expression (DAX) queries; Link
• Understand the difference between partitioning for load performance versus query performance in SSAS; Link, Link1 (Lazy Aggregations)
• Appropriately index a fact table; Link
• Optimize Analysis Services cubes in SQL Server Data Tools; Link
• Create aggregations; Link, Link1
Plan for scalability
• Change binding options for partitions, Cube synchronization; Link, Link1
• Choose the appropriate Multidimensional OLAP (MOLAP), Relational OLAP (ROLAP), and Hybrid OLAP (HOLAP) storage modes; Link
Plan and manage upgrades
• Plan change management for a BI solution; Link, Link1
• Cluster upgrade (from the cmd); Link
Maintain server health
• Design an automation strategy; Link

Design BI infrastructure (15–20%)
Design a security strategy
• Configure security and impersonation between SQL Server service, analysis services, and front end; Link, Link2, Link3,
• Implement Dynamic Dimension Security within a cube (data level security); Link, Link1, Link2
• Configure security for an extranet environment; Link
• Configure Kerberos security; Configure contrained delegation; Link, Link2
• Design authentication mechanisms; Link
• Design security tests; Link, Link
• Build secure solutions end to end; Link
• Design roles for calculated measures; Link
• Understand the tradeoffs between regular SSAS security and dynamic security; Link
Design a SQL partitioning strategy
• Choose the proper partitioning strategy for the data warehouse and cube; Link, Link1 (video)
• implement a parallel load to fact tables by using partition switching; Link, Link1 (video)
• use data compression; Link
Design a high availability and disaster recovery strategy
• Back up and restore SSAS databases; Link
• Back up and restore SSRS databases; Link
• Move and restore the SSIS Catalog; Link
• Design an AlwaysON solution; Link
• Design a logging and auditing strategy; Link, Link1
• Design a new SSIS logging infrastructure (catalog views); Link, Link1, Link2
• Validate data is balancing and reconciling correctly; Link

Design a reporting solution (20–25%)
Design a Reporting Services dataset
• Design appropriate data query parameters, create appropriate SQL queries; Link
• Create appropriate DAX queries for an application (Related); Link, Link1
• Manage data rights and security; Link
• Extract data from analysis services by using MDX queries; Link
• Balance query-based processing versus filter-based processing; Link
• Manage data sets through the use of stored procedures; Link
Manage Excel Services/reporting for SharePoint
• Configure data refresh schedules for PowerPivot published to SharePoint; Link
• Publish BI info to SharePoint; Link, Link1, Link2
• Use SharePoint to accomplish BI administrative tasks; Link
• Install and configure Power View, Link
• Publish PowerPivot and Power View to SharePoint (WebParts); Link
• PwerPivot security; Link, Link1, Link2 (usage data collection)
Design a data acquisition strategy
• Identify the data sources that need to be used to pull in the data; Link
• Determine the changes (incremental data) in the data source (time window), Link, Link
• Identify the relationship and dependencies between the data sources; Link
• Determine who can access which data; Link, Link1, Link2
• Determine what data can be retained for how long (regulatory compliance, data archiving, aging, caching); Link, Link1
• Design a data movement strategy, profile source data; Link
• Customize data acquisition using DAX with reporting services data sources; Link (video),
Plan and manage reporting services configuration
• Choose the appropriate reporting services requirements (including native mode and SharePoint mode) Link
• Reporting accounts, athentication, SPN; Link, Link1, Link2
Design BI reporting solution architecture
• Linked drill-down reports, drill-through reports, and sub reports; Link, Link1, Link2
• Design report migration strategies; Link, Link1
• Access report services API; Link, Link1
• Design code-behind strategies, assemblies; Link, Link1
• Identify when to use Reporting Services (RS), Report Builder (RB), or Power View; Link,
• Design and implement context transfer when interlinking all types of reports (RS, RB, Power View, Excel); Link, Link1
• Implement BI tools for reporting in SharePoint (Excel Services versus PowerView versus Reporting Services); Link
• Select a subscription strategy; Link, Link1
• Enable Data Alerts; Link
• Design map visualization; Link, Link1

Design BI data models (30–35%)
Design the data warehouse
• Design a data model that is optimized for reporting; Link
• Design enterprise data warehouse (EDW), Identify design best practices; Link, Link1
• Design OLAP cubes, Hierarchies; Link, Link1
• Choose between natural keys and surrogate keys when designing the data warehouse; Link
• Use SQL Server to design, implement, and maintain a data warehouse; Link
• Design, implement, and maintain partitioning; Link; Link1, Link2 (remote partitions)
• Design, implement, and maintain slowly changing dimensions (SCD); Link (video), Link1, Link2
• Design, implement, and maintain change data capture (CDC); Link
• Design, implement, and maintain Index Views; Link
• Design, implement, and maintain column store indexes; Link
• Implement a many-to-many relationship in an OLAP cube (intermediate measure group); Link, Link1
• Dimension types (junk, degenerate); Link, Link1
• Design a data mart/warehouse in reverse from an Analysis Services cube; Link
• Implement incremental data load; Link,
• Choose between performing aggregation operations in the SSIS pipeline or the relational engine; Link
Design a schema
• Multidimensional modeling starting from a star or snowflake schema; Link, Link1
• Dimension table key selection; Link
• Design relational modeling for a Data Mart; Link
Design cube architecture
• Partition cubes and build aggregation strategies for the separate partitions; Link, Link1, Link2
• Design a data model; choose the proper partitioning strategy for the data warehouse and cube; Link
• Design the data file layout; Link, Link1
• Identify the aggregation method for a measure in a MOLAP cube; Link
• Performance tune a MOLAP cube using aggregations; Link
• Performance tune using Usage Based Optimization; Link1
• Design a data source view; Link, Link1
• Design for cube drill-through; Link, Link1
• Writeback; Link
• Choose the correct grain of data to store in a measure group; Link
• Design analysis services processing by using indexes, indexed views, and order by statements; Link, Link1
Design fact tables
• Design a data warehouse that supports many to many dimensions with factless fact tables; Link, Link1, Link2
Design BI semantic models
• Plan for a multidimensional cube; Link
• Tabular model modes, DirectQuery; Link, Link1
• Relationships between tables; Link
• Choose between multidimensional and tabular, depending on the type of data and workload; Link, Link1
Design and create MDX calculations
• Design MDX queries, identify the structures of MDX; Link
• Common MDX functions (tuples, sets, TopCount, SCOPE, This, VisualTotals, Generate, Exists, LAG, Root and more), Identify which MDX statement would return the required result, Implement a custom MDX or logical solution for a pre-prepared case task; Link, Link1, Link2, Link3, Link4
• Create calculated members in an MDX statement; Link, Link1
• Cube calculation script; Link

Design an ETL solution (10–15%)
Design SSIS package execution
• Use the new project deployment model; Link, Link1
• Pass values at execution time; Link
• Share parameters between packages; Link
• Plan for incremental loads versus full loads; Link, Link1
• Optimize execution by using Balanced Data Distributor (BDD); Link
• Choose optimal processing strategy (including Script transform, flat file incremental loads, Record Sets and Derived Column transform, Distinct Sort); Link, Link1, Link2
• Connection Manager options; Link
Plan to deploy SSIS solutions
• Deploy the package to another server with different security requirements; Link
• Secure integration services packages that are deployed at the file system; Link
• Demonstrate awareness of SSIS packages/projects and how they interact with environments (including recoverability), Link
• Decide between performing aggregation operations in the SSIS pipeline or the relational engine, Link, Link1
• Plan to automate SSIS deployment; Link, Link1, Link2
• Plan the administration of the SSIS Catalog database; Link
Design package configurations for SSIS packages
• Avoid repeating configuration information entered in SSIS packages, and use configuration files; Link, Link1
• SSIS logging; Link


Leave a Comment here

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s