70-467 Exam Questions
122 real 70-467 exam questions with expert-verified answers and explanations. Page 1 of 3.
- Question #5Design BI data models
A SQL Server Analysis Services (SSAS) cube contains a large measure group. The fact table supporting the measure group is loaded with new data throughout the day. You have the foll...
SSAS partitioningMOLAPROLAPHOLAP - Question #6Design BI data models
You are developing the database schema for a SQL Server Analysis Services (SSAS) BI Semantic Model (BISM). The BISM will be based on the schema displayed in the following graphic....
BISMmultidimensional modeltabular modelmany-to-many relationship - Question #10Design a data warehousing solution
You are designing a partitioning strategy for a large fact table in a Manufacturing data warehouse. Tens of millions of new inventory fact records are loaded into the data warehous...
table partitioningfact tabledata compressionquery performance - Question #11Design a reporting solution
You are designing a subscription strategy for a SQL Server Reporting Services (SSRS) report. You have an application that populates a table with user-specific subscription schedule...
data-driven subscriptionsSSRS subscriptionsemail deliveryuser preferences - Question #12Design a reporting solution
You are modifying a SQL Server Reporting Services (SSRS) report for a SQL Server Analysis Services (SSAS) cube. The report defines a report parameter of data type Date/Time with wh...
MDX queriesSSRS parametersSSAS integrationdate filtering - Question #14Design a reporting solution
You are designing a strategy for an enterprise reporting solution that uses SQL Server Reporting Services (SSRS). Many of the SSRS reports will use common utilities and functions,...
SSRS assembliesreport brandingenterprise reportingcode reuse - Question #17Design an ETL solution (Extract, Transform, and Load)
You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define th...
SSIS deployment modelproject deploymentSSISDB catalogconnection strings - Question #21Design a data warehousing solution
You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of new records are loaded into the data warehouse weekly, outside of business...
table partitioningfact tabledata compressionquery performance - Question #22Design an ETL solution (Extract, Transform, and Load)
You are designing an extract, transform, load (ETL) process for loading data from a SQL Server database into a large fact table in a data warehouse each day with the prior day's sa...
ETL designpartition switchingfact table loadingtransaction log optimization - Question #25Design BI data models
You are creating a Multidimensional Expressions (MDX) calculation for Projected Revenue in a cube. For Customer A, Projected Revenue is defined as 150 percent of the Total Sales fo...
MDX calculationsconditional logiccube measuresSSAS - Question #26Design BI data models
You are developing the database schema for a SQL Server Analysis Services (SSAS) BI Semantic Model (BISM). The BISM will be based on the schema displayed in the following graphic....
BISMmany-to-many relationshipsmultidimensional modeltabular model - Question #28Design a data warehousing solution
You are modifying a star schema data mart that feeds order data from a SQL Azure database into a SQL Server Analysis Services (SSAS) cube. The data mart contains two large tables t...
star schemasurrogate keydimension designdata mart - Question #29Design BI data models
You are defining a named set by using Multidimensional Expressions (MDX) in a sales cube. The cube includes a Product dimension that contains a Category hierarchy and a Color attri...
MDX named setsattribute filteringdimension hierarchiesSSAS - Question #30Design BI data models
An existing cube dimension that has 30 attribute hierarchies is performing very poorly. You have the following requirements: - Implement drill-down browsing. - Reduce the number of...
attribute hierarchiesdimension optimizationuser-defined hierarchiesSSAS performance - Question #33Design an ETL solution (Extract, Transform, and Load)
You are designing an extract, transform, load (ETL) process for loading data from a SQL Azure database into a large fact table in a data warehouse each day with the prior day's sal...
fact table partitioningETL loadingpartition switchingtransaction log - Question #38Design BI data models
You are designing a multidimensional OLAP (MOLAP) cube. The MOLAP cube must meet the following requirements: Ensure that workloads for aggregation tuning can be automatically colle...
MOLAP aggregationquery loggingaggregation tuningSSAS performance - Question #39Design a data warehousing solution
You are designing a fact table in a SQL Server database. The fact table must meet the following requirements: - Include a columnstore index. - Allow users to choose up to 10 dimens...
columnstore indexfact table designdimension keysdata warehousing - Question #41Design BI data models
You are creating a Multidimensional Expressions (MDX) calculation for Projected Revenue in a cube. For Product A, Projected Revenue is defined as 150 percent of the Total Sales of...
MDX calculationsconditional logiccube measuresSSAS - Question #42Design BI data models
You are defining a named set by using Multidimensional Expressions (MDX) in a sales cube. The cube includes a Customer dimension that contains a Geography hierarchy and a Gender at...
MDX named setsattribute filteringdimension hierarchiesSSAS - Question #44Design an ETL solution (Extract, Transform, and Load)
You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart. You hav...
SSIS Data Profiling taskdata qualityexception reportsnull ratios - Question #45Design an ETL solution (Extract, Transform, and Load)
You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define th...
SSIS deployment modelproject deploymentSSISDB loggingconnection managers - Question #47Plan business intelligence (BI) infrastructure
You need to fix the PowerPivot data refresh problem by using the least amount of administrative effort. What should you do?
PowerPivotdata refreshPowerPivot Configuration ToolSharePoint - Question #48Design an ETL solution (Extract, Transform, and Load)
You need to grant appropriate permissions to the SSISOwners SQL Server login. What should you do?
SSIS securitySSISDBSQL Server loginpermissions - Question #49Design BI data models
You need to configure the Scenario attribute to ensure that business users appropriately query the Sales Plan measure. What should you do? (Each correct answer presents part of the...
SSAS attribute hierarchyIsAggregatableDefaultMemberdimension design - Question #53Design an ETL solution (Extract, Transform, and Load)
You need to configure package execution logging to meet the requirements. What should you do?
SSIS loggingSSIS catalogexecution logginglogging level - Question #54Design a reporting solution
You need to create the Sales Reporting shared SSRS data source. Which SSRS data connection type should you use?
SSRS data sourceSQL Server connectionshared data sourcedata connection type - Question #55Design a reporting solution
You need to select an appropriate tool for creating the Regional Sales report. Which tool or tools should you use? (Each correct answer presents a complete solution. Choose all tha...
Power ViewExcel PivotTableReport BuilderMatrix report - Question #56Design a reporting solution
You need to select an appropriate tool for creating the Regional Sales report. Which tools or tools should you use? (Each Answer presents a complete solution. Choose all that apply...
Power View multiplesExcel PivotTableReport Buildertool selection - Question #57Design BI data models
You need to create the calculation for SalespersonName. What should you do? (Each Answer presents a complete solution. Choose all that apply.)
calculated columnDimSalespersondata warehousetabular model - Question #58Plan business intelligence (BI) infrastructure
You need to configure data refresh for the Manufacturing Performance PowerPivot workbook. What should you do? (Each Answer presents part of the solution. Choose ail that apply.)
PowerPivot data refreshSSAS tabular modeSharePointTimer Job - Question #60Design BI infrastructure
You need to configure security for the SSRS instance on SSRS01 to connect to SSAS and minimize downtime. What should you do? (Each correct answer presents part of the solution. Cho...
SSRS securityKerberosSPN registrationNegotiate authentication - Question #61Plan business intelligence (BI) infrastructure
You need to perform the pre-upgrade database backup operation by using SQL Server Management Studio (SSMS). How should you configure the backup options?
backup compressionSSMSdatabase backupupgrade preparation - Question #62Design BI data models
You need to implement the Customer Sales and Manufacturing data models. What should you do? (Each correct answer presents a partial solution. Choose all that apply.)
SSAS tabular modeSSDT deploymentdata model migrationSSAS instance - Question #63Plan business intelligence (BI) infrastructure
You need to re-establish subscriptions on SSRS01. What should you do?
SSRS subscriptionsfailovercluster nodeupgrade - Question #64Role of Transact-SQL and the Database Engine in Enterprise IT
You need to roll back the compatibility level of the Research database. What should you do?
database compatibility leveldatabase restorerollbackALTER DATABASE - Question #66Design BI data models
You need to develop a BISM that meets the business requirements for ad-hoc and daily operational analysis. You must minimize development effort. Which development approach and mode...
BISMtabular vs multidimensionalDirectQueryHOLAP - Question #67Plan business intelligence (BI) infrastructure
You need to use SQL Server Management Studio (SSMS) to make the SSAS databases available for application testing. What should you do?
SSAS database restoreSSMSdatabase availabilitytesting environment - Question #68Design a reporting solution
You need to configure SSRS to send the required notification messages. Which configuration settings should you use? (Each correct answer presents a partial solution. Choose all tha...
SSRS email deliverySMTP configurationRSReportServer.configsubscription delivery - Question #69Design BI data models
You need to implement the security requirement for the sales representatives. Which MDX expression should you use?
MDX securitydimension securitySSAS database rolescube access control - Question #70Design a reporting solution
You need to use Reporting Services Configuration Manager to configure SSRS to complete the installation on SSRS02. What should you do? (Each correct answer presents a partial solut...
SSRS scale-outencryption keyReport Manager URLexecution account - Question #71Design a reporting solution
You need to re-establish subscriptions on SSRS01. What should you do?
SSRS subscriptionsSQL Server Agentreport schedulingSSRS dependencies - Question #72Design a reporting solution
You need to design the recovery strategy for SSRS01. What should the strategy include? (Each Answer presents part of the solution. Choose all that apply.)
SSRS disaster recoveryReportServer databaseencryption key backupSQL Server Agent jobs - Question #73Design an ETL solution (Extract, Transform, and Load)
You need to identify changes in the financial database. What should you do?
change data captureincremental ETLCDCdata extraction - Question #74Design an ETL solution (Extract, Transform, and Load)
You need to create the Package Activity report. What should you do?
SSIS catalogSSIS loggingcatalog.executionsexecution reporting - Question #75Design BI data models
You need to implement the aggregation designs for the cube. What should you do?
SSAS aggregationsAggregation Design Wizardcube optimizationMOLAP - Question #76Design a data warehousing solution
You need to slice data by the CouponUsed, OnSale, and Closeout columns. What should you do?
junk dimensionlow-cardinality flagsdimension designdata warehousing - Question #77Design BI data models
You need to design a cube partitioning strategy to be implemented as the cube size increases. What should you do?
SSAS partitioningremote partitionscube scalingpartition strategy - Question #78Design a data warehousing solution
You need to choose the appropriate key to use when designing a dimension table based on the Customer table. What should you do?
surrogate keydimension designnatural keydata warehousing - Question #79Design a data warehousing solution
You need to implement the product dimension. What should you do?
SCD Type 2product dimensionslowly changing dimensiondimension history - Question #80Design BI infrastructure
You need to scale out SSAS. What should you do?
SSAS synchronizationcube scale-outSSAS high availabilityread-only replica