Published: August 10, 2014
Languages: English, Japanese
Audiences: IT professionals
Technology: Microsoft SQL Server 2014
Credit toward certification: MCP, MCSE
Skills measured
This exam measures your ability to accomplish the technical tasks listed below. View video tutorials about the variety of question types on Microsoft exams.
Please note that the questions may test on, but will not be limited to, the topics described in the bulleted text.
Do you have feedback about the relevance of the skills measured on this exam? Please send Microsoft your comments. All feedback will be reviewed and incorporated as appropriate while still maintaining the validity and reliability of the certification process. Note that Microsoft will not respond directly to your feedback. We appreciate your input in ensuring the quality of the Microsoft Certification program.
If you have concerns about specific questions on this exam, please submit an exam challenge.
If you have other questions or feedback about Microsoft Certification exams or about the certification program, registration, or promotions, please contact your Regional Service Center.
Build an analysis services multidimensional database
Implement a cube
Use SQL Server Data Tools - Business Intelligence (SSDT-BI) to build the cube; use SSDT-BI to do non-additive or semi-additive measures in a cube, define measures, specify perspectives, define translations, define dimension usage, define cube-specific dimension properties, define measure groups, implement reference dimensions, implement many-to-many relationships, implement fact relationships, implement role-playing relationships, create and manage linked measure groups and linked dimensions, create actions
Implement custom logic in a data model
Define key performance indicators (KPIs); define calculated members; create relative measures (growth, YoY, same period last year), percentage of total using MDX; named sets; add Time Intelligence; implement ranking and percentile; define MDX script to import partial PowerPivot model
Select an appropriate model for data analysis
Select Tabular versus Multidimensional based on scalability needs, traditional hierarchical, data volume; select appropriate organizational BI, such as corporate BI or PowerBI, and team and personal BI needs and data status
Manage, maintain, and troubleshoot a SQL Server Analysis Services (SSAS) database
Process data models
Define processing of tables or partitions for tabular and multidimensional models; define processing of databases, cubes, and dimensions for multidimensional models; select full processing versus incremental processing; define remote processing; define lazy aggregations; automate with Analysis Management Objects (AMO) or XML for Analysis (XMLA); process and manage partitions by using PowerShell
Install and maintain an SSAS instance
Install SSAS; install development tools; identify development and production installation considerations; upgrade SSAS instance; define data file and program file location; plan for Administrator accounts; define server and database level security; support scale-out read-only; update SSAS (service packs); install and maintain each instance type of Analysis Services, including PowerPivot; restore and import PowerPivot; back up and restore by using PowerShell
Build a tabular data model
Implement a tabular data model
Define tables, import data, define calculated columns, define relationships, define hierarchies and perspectives, manage visibility of columns and tables, embed links, optimize BISM for Power View, mark a date table, sort a column by another column
Implement data access for a tabular data model
Manage partitions, processing, select xVelocity versus DirectQuery for data access
Build a report with SQL Server Reporting Services (SSRS)
Design a report
Select report components (crosstab report, Tablix, design chart, data visualization components), design report templates (Report Definition Language), identify the data source and parameters; design 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
Manage a report environment
Manage subscriptions and subscription settings; define data-driven subscriptions; manage data sources; integrate SharePoint Server; define email delivery settings; manage the number of snapshots; manage schedules, running jobs, and report server logs; manage report server databases; manage the encryption keys; set up the execution log reporting; review the reports; configure site-level settings; design report lifecycle; automate management of reporting services; create a report organization structure; install and configure reporting services; deploy custom assemblies
Configure report data sources and datasets
Select appropriate query types (stored procedure versus table versus text only); configure parameterized connection strings (dynamic connection strings); define filter location (dataset versus query); configure data source options, for example, extract and connect to multiple data sources; shared and embedded data sources and datasets; use custom expressions in data sources; connect to Microsoft Azure SQL database; connect to Microsoft Azure Marketplace; implement DAX and MDX queries to retrieve appropriate data sets; work with non-relational data sources, such as XML or SharePoint lists; connect to HDInsight Server
Plan business intelligence (BI) infrastructure
Plan for performance
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 named queries in a data source view; analyze and optimize performance, including Multidimensional Expression (MDX) and Data Analysis Expression (DAX) queries; understand the difference between partitioning for load performance versus query performance in SSAS; appropriately index a fact table; optimize Analysis Services cubes in SQL Server Data Tools; create aggregations
Design BI infrastructure
Design a high availability and disaster recovery strategy
Design a recovery strategy, back up and restore SSAS databases, back up and restore SSRS databases, move and restore the SSIS Catalog, design an AlwaysON solution
Design a reporting solution
Design a Reporting Services dataset
Design appropriate data query parameters, create appropriate SQL queries, create appropriate DAX queries for an application, manage data rights and security, extract data from analysis services by using MDX queries, balance query-based processing versus filter-based processing, manage data sets through the use of stored procedures
Manage Excel Services/reporting for SharePoint
Configure data refresh schedules for PowerPivot published to SharePoint, publish BI info to SharePoint, use SharePoint to accomplish BI administrative tasks, install and configure Power View, publish PowerPivot and Power View to SharePoint
Design BI reporting solution architecture
Linked drill-down reports, drill-through reports, and sub reports; design report migration strategies; access report services API; design code-behind strategies; identify when to use Reporting Services (RS), Report Builder (RB), or Power View; design and implement context transfer when interlinking all types of reports (RS, RB, Power View, Excel); implement BI tools for reporting in SharePoint (Excel Services versus PowerView versus Reporting Services); select a subscription strategy; enable Data Alerts; design map visualization
Design BI data models
Design the data warehouse
Design a data model that is optimized for reporting; design and 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 SQL Server to design, implement, and maintain a data warehouse, including partitioning, slowly changing dimensions (SCD), change data capture (CDC), Index Views, and column store indexes; 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; implement incremental data load; choose between performing aggregation operations in the SSIS pipeline or the relational engine
Design cube architecture
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; identify the aggregation method for a measure in a MOLAP cube; performance tune a MOLAP cube using aggregations; design a data source view; design for cube drill-through and write back actions; choose the correct grain of data to store in a measure group; design analysis services processing by using indexes, indexed views, and order by statements
Design an ETL solution
Design SSIS package execution
Use the new project deployment model; pass values at execution time; share parameters between packages; plan for incremental loads versus full loads; optimize execution by using Balanced Data Distributor (BDD); choose optimal processing strategy (including Script transform, flat file incremental loads, and Derived Column transform)
Plan to deploy SSIS solutions
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 (including recoverability), decide between performing aggregation operations in the SSIS pipeline or the relational engine, plan to automate SSIS deployment, plan the administration of the SSIS Catalog database
QUESTION 1
You need to identify the reports that produce the errors that Marc is receiving.
What should you do?
A. Write a query by using the Subscriptions table in the report server database.
B. Use the Windows Event Viewer to search the Application log for errors.
C. Write a query by using the ExecutionLog3 view in the report server database.
D. Search the ReportServerService_<timestamp>.log file for errors.
Answer: C
QUESTION 2
You need to deploy the StandardReports project.
What should you do? (Each correct answer presents a complete solution. Choose all that apply.)
A. Deploy the project from SQL Server Data Tools (SSDT).
B. Use the Analysis Services Deployment utility to create an XMLA deployment script.
C. Use the Analysis Services Deployment wizard to create an MDX deployment script.
D. Use the Analysis Services Deployment wizard to create an XMLA deployment script.
Answer: A,D
Explanation: There are several methods you can use to deploy a tabular model project. Most of the deployment methods that can be used for other Analysis Services projects, such as multidimensional, can also be used to deploy tabular model projects.
A: Deploy command in SQL Server Data Tools
The Deploy command provides a simple and intuitive method to deploy a tabular model project from the SQL Server Data Tools authoring environment.
Caution:
This method should not be used to deploy to production servers. Using this method can overwrite certain properties in an existing model.
D: The Analysis Services Deployment Wizard uses the XML output files generated from a Microsoft SQL Server Analysis Services project as input files. These input files are easily modifiable to customize the deployment of an Analysis Services project. The generated deployment script can then either be immediately run or saved for later deployment.
Incorrect:
not B: The Microsoft.AnalysisServices.Deployment utility lets you start the Microsoft SQL Server Analysis Services deployment engine from the command prompt. As input file, the utility uses the XML output files generated by building an Analysis Services project in SQL Server Data Tools (SSDT).
QUESTION 3
You need create the data source view for the StandardReports project.
What should you do?
A. Generate a relational schema from the dimensions and cubes by using the Schema Generation wizard.
B. Create a data source, connect it to the data warehouse, and then use the Data Source View wizard.
C. Execute the Import from Table wizard and then use the Data Source View wizard.
D. Create a new data source view and then use the Import from Table wizard.
Answer: B
QUESTION 4
You need to ascertain why Marc did not receive his reports.
What should you do?
A. Search the ReportServerService_<timestamp>.log file for errors.
B. Search the registry for errors.
C. Use the Windows Event Viewer to search the Application log for errors.
D. Use SQL Server Management Studio to search the SQL Server logs for errors.
Answer: B
QUESTION 5
You need to create a measure for DOD sales.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Specify a date table by using a Mark as Date table.
B. Use the Data Analysis Expressions (DAX) PARALLELPERIOD() function.
C. Use the Business Intelligence Wizard to define time intelligence.
D. Use the Multidimensional Expressions (MDX) LAG() function.
Answer: A,C
Explanation: * From scenario:
A measure must be created to calculate day-over-day (DOD) sales by region based on order date.
A: Specify Mark as Date Table for use with Time Intelligence (SSAS Tabular)
In order to use time intelligence functions in DAX formulas, you must specify a date table and a unique identifier (datetime) column of the Date data type. Once a column in the date table is specified as a unique identifier, you can create relationships between columns in the date table and any fact tables.
C: The time intelligence enhancement is a cube enhancement that adds time calculations (or time views) to a selected hierarchy. This enhancement supports the following categories of calculations:
Period to date.
Period over period growth. Moving averages.
Parallel period comparisons.
Languages: English, Japanese
Audiences: IT professionals
Technology: Microsoft SQL Server 2014
Credit toward certification: MCP, MCSE
Skills measured
This exam measures your ability to accomplish the technical tasks listed below. View video tutorials about the variety of question types on Microsoft exams.
Please note that the questions may test on, but will not be limited to, the topics described in the bulleted text.
Do you have feedback about the relevance of the skills measured on this exam? Please send Microsoft your comments. All feedback will be reviewed and incorporated as appropriate while still maintaining the validity and reliability of the certification process. Note that Microsoft will not respond directly to your feedback. We appreciate your input in ensuring the quality of the Microsoft Certification program.
If you have concerns about specific questions on this exam, please submit an exam challenge.
If you have other questions or feedback about Microsoft Certification exams or about the certification program, registration, or promotions, please contact your Regional Service Center.
Build an analysis services multidimensional database
Implement a cube
Use SQL Server Data Tools - Business Intelligence (SSDT-BI) to build the cube; use SSDT-BI to do non-additive or semi-additive measures in a cube, define measures, specify perspectives, define translations, define dimension usage, define cube-specific dimension properties, define measure groups, implement reference dimensions, implement many-to-many relationships, implement fact relationships, implement role-playing relationships, create and manage linked measure groups and linked dimensions, create actions
Implement custom logic in a data model
Define key performance indicators (KPIs); define calculated members; create relative measures (growth, YoY, same period last year), percentage of total using MDX; named sets; add Time Intelligence; implement ranking and percentile; define MDX script to import partial PowerPivot model
Select an appropriate model for data analysis
Select Tabular versus Multidimensional based on scalability needs, traditional hierarchical, data volume; select appropriate organizational BI, such as corporate BI or PowerBI, and team and personal BI needs and data status
Manage, maintain, and troubleshoot a SQL Server Analysis Services (SSAS) database
Process data models
Define processing of tables or partitions for tabular and multidimensional models; define processing of databases, cubes, and dimensions for multidimensional models; select full processing versus incremental processing; define remote processing; define lazy aggregations; automate with Analysis Management Objects (AMO) or XML for Analysis (XMLA); process and manage partitions by using PowerShell
Install and maintain an SSAS instance
Install SSAS; install development tools; identify development and production installation considerations; upgrade SSAS instance; define data file and program file location; plan for Administrator accounts; define server and database level security; support scale-out read-only; update SSAS (service packs); install and maintain each instance type of Analysis Services, including PowerPivot; restore and import PowerPivot; back up and restore by using PowerShell
Build a tabular data model
Implement a tabular data model
Define tables, import data, define calculated columns, define relationships, define hierarchies and perspectives, manage visibility of columns and tables, embed links, optimize BISM for Power View, mark a date table, sort a column by another column
Implement data access for a tabular data model
Manage partitions, processing, select xVelocity versus DirectQuery for data access
Build a report with SQL Server Reporting Services (SSRS)
Design a report
Select report components (crosstab report, Tablix, design chart, data visualization components), design report templates (Report Definition Language), identify the data source and parameters; design 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
Manage a report environment
Manage subscriptions and subscription settings; define data-driven subscriptions; manage data sources; integrate SharePoint Server; define email delivery settings; manage the number of snapshots; manage schedules, running jobs, and report server logs; manage report server databases; manage the encryption keys; set up the execution log reporting; review the reports; configure site-level settings; design report lifecycle; automate management of reporting services; create a report organization structure; install and configure reporting services; deploy custom assemblies
Configure report data sources and datasets
Select appropriate query types (stored procedure versus table versus text only); configure parameterized connection strings (dynamic connection strings); define filter location (dataset versus query); configure data source options, for example, extract and connect to multiple data sources; shared and embedded data sources and datasets; use custom expressions in data sources; connect to Microsoft Azure SQL database; connect to Microsoft Azure Marketplace; implement DAX and MDX queries to retrieve appropriate data sets; work with non-relational data sources, such as XML or SharePoint lists; connect to HDInsight Server
Plan business intelligence (BI) infrastructure
Plan for performance
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 named queries in a data source view; analyze and optimize performance, including Multidimensional Expression (MDX) and Data Analysis Expression (DAX) queries; understand the difference between partitioning for load performance versus query performance in SSAS; appropriately index a fact table; optimize Analysis Services cubes in SQL Server Data Tools; create aggregations
Design BI infrastructure
Design a high availability and disaster recovery strategy
Design a recovery strategy, back up and restore SSAS databases, back up and restore SSRS databases, move and restore the SSIS Catalog, design an AlwaysON solution
Design a reporting solution
Design a Reporting Services dataset
Design appropriate data query parameters, create appropriate SQL queries, create appropriate DAX queries for an application, manage data rights and security, extract data from analysis services by using MDX queries, balance query-based processing versus filter-based processing, manage data sets through the use of stored procedures
Manage Excel Services/reporting for SharePoint
Configure data refresh schedules for PowerPivot published to SharePoint, publish BI info to SharePoint, use SharePoint to accomplish BI administrative tasks, install and configure Power View, publish PowerPivot and Power View to SharePoint
Design BI reporting solution architecture
Linked drill-down reports, drill-through reports, and sub reports; design report migration strategies; access report services API; design code-behind strategies; identify when to use Reporting Services (RS), Report Builder (RB), or Power View; design and implement context transfer when interlinking all types of reports (RS, RB, Power View, Excel); implement BI tools for reporting in SharePoint (Excel Services versus PowerView versus Reporting Services); select a subscription strategy; enable Data Alerts; design map visualization
Design BI data models
Design the data warehouse
Design a data model that is optimized for reporting; design and 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 SQL Server to design, implement, and maintain a data warehouse, including partitioning, slowly changing dimensions (SCD), change data capture (CDC), Index Views, and column store indexes; 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; implement incremental data load; choose between performing aggregation operations in the SSIS pipeline or the relational engine
Design cube architecture
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; identify the aggregation method for a measure in a MOLAP cube; performance tune a MOLAP cube using aggregations; design a data source view; design for cube drill-through and write back actions; choose the correct grain of data to store in a measure group; design analysis services processing by using indexes, indexed views, and order by statements
Design an ETL solution
Design SSIS package execution
Use the new project deployment model; pass values at execution time; share parameters between packages; plan for incremental loads versus full loads; optimize execution by using Balanced Data Distributor (BDD); choose optimal processing strategy (including Script transform, flat file incremental loads, and Derived Column transform)
Plan to deploy SSIS solutions
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 (including recoverability), decide between performing aggregation operations in the SSIS pipeline or the relational engine, plan to automate SSIS deployment, plan the administration of the SSIS Catalog database
QUESTION 1
You need to identify the reports that produce the errors that Marc is receiving.
What should you do?
A. Write a query by using the Subscriptions table in the report server database.
B. Use the Windows Event Viewer to search the Application log for errors.
C. Write a query by using the ExecutionLog3 view in the report server database.
D. Search the ReportServerService_<timestamp>.log file for errors.
Answer: C
QUESTION 2
You need to deploy the StandardReports project.
What should you do? (Each correct answer presents a complete solution. Choose all that apply.)
A. Deploy the project from SQL Server Data Tools (SSDT).
B. Use the Analysis Services Deployment utility to create an XMLA deployment script.
C. Use the Analysis Services Deployment wizard to create an MDX deployment script.
D. Use the Analysis Services Deployment wizard to create an XMLA deployment script.
Answer: A,D
Explanation: There are several methods you can use to deploy a tabular model project. Most of the deployment methods that can be used for other Analysis Services projects, such as multidimensional, can also be used to deploy tabular model projects.
A: Deploy command in SQL Server Data Tools
The Deploy command provides a simple and intuitive method to deploy a tabular model project from the SQL Server Data Tools authoring environment.
Caution:
This method should not be used to deploy to production servers. Using this method can overwrite certain properties in an existing model.
D: The Analysis Services Deployment Wizard uses the XML output files generated from a Microsoft SQL Server Analysis Services project as input files. These input files are easily modifiable to customize the deployment of an Analysis Services project. The generated deployment script can then either be immediately run or saved for later deployment.
Incorrect:
not B: The Microsoft.AnalysisServices.Deployment utility lets you start the Microsoft SQL Server Analysis Services deployment engine from the command prompt. As input file, the utility uses the XML output files generated by building an Analysis Services project in SQL Server Data Tools (SSDT).
QUESTION 3
You need create the data source view for the StandardReports project.
What should you do?
A. Generate a relational schema from the dimensions and cubes by using the Schema Generation wizard.
B. Create a data source, connect it to the data warehouse, and then use the Data Source View wizard.
C. Execute the Import from Table wizard and then use the Data Source View wizard.
D. Create a new data source view and then use the Import from Table wizard.
Answer: B
QUESTION 4
You need to ascertain why Marc did not receive his reports.
What should you do?
A. Search the ReportServerService_<timestamp>.log file for errors.
B. Search the registry for errors.
C. Use the Windows Event Viewer to search the Application log for errors.
D. Use SQL Server Management Studio to search the SQL Server logs for errors.
Answer: B
QUESTION 5
You need to create a measure for DOD sales.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Specify a date table by using a Mark as Date table.
B. Use the Data Analysis Expressions (DAX) PARALLELPERIOD() function.
C. Use the Business Intelligence Wizard to define time intelligence.
D. Use the Multidimensional Expressions (MDX) LAG() function.
Answer: A,C
Explanation: * From scenario:
A measure must be created to calculate day-over-day (DOD) sales by region based on order date.
A: Specify Mark as Date Table for use with Time Intelligence (SSAS Tabular)
In order to use time intelligence functions in DAX formulas, you must specify a date table and a unique identifier (datetime) column of the Date data type. Once a column in the date table is specified as a unique identifier, you can create relationships between columns in the date table and any fact tables.
C: The time intelligence enhancement is a cube enhancement that adds time calculations (or time views) to a selected hierarchy. This enhancement supports the following categories of calculations:
Period to date.
Period over period growth. Moving averages.
Parallel period comparisons.
