Developing SQL Data Models v7.0
Question 1
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a Microsoft SQL Server Analysis Services (SSAS) instance that is configured to use multidimensional mode. You create the following cube:
Users need to be able to analyze sales by color.
You need to create a dimension that contains all of the colors for products sold by the company.
Which relationship type should you use between the InternetSales table and the new dimension?
- A. no relationship
- B. regular
- C. fact
- D. referenced
- E. many-to-many
- F. data mining
Answer : B
Explanation:
A regular dimension relationship between a cube dimension and a measure group exists when the key column for the dimension is joined directly to the fact table.
References: https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional- models-olap-logical-cube-objects/dimension-relationships
Question 2
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You deploy a tabular data model to an instance of Microsoft SQL Server Analysis Services
(SSAS). The model uses an in-memory cache to store and query data. The data set is already the same size as the available RAM on the server. Data volumes are likely to continue to increase rapidly.
Your data model contains multiple calculated tables.
The data model must begin processing each day at 2:00 and processing should be complete by 4:00 the same day. You observe that the data processing operation often does not complete before 7:00. This is adversely affecting team members.
You need to improve the performance.
Solution: Install solid-state disk drives to store the tabular data model.
Does the solution meet the goal?
- A. Yes
- B. No
Answer : B
Explanation:
By default, tabular models use an in-memory cache to store and query data. When tabular models query data residing in-memory, even complex queries can be incredibly fast.
However, there are some limitations to using cached data. Namely, large data sets can exceed available memory, and data freshness requirements can be difficult if not impossible to achieve on a regular processing schedule.
DirectQuery overcomes these limitations while also leveraging RDBMS features making query execution more efficient.
With DirectQuery: +
References:https://docs.microsoft.com/en-us/sql/analysis-services/tabular- models/directquery-mode-ssas-tabular
Question 3
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You deploy a tabular data model to an instance of Microsoft SQL Server Analysis Services
(SSAS). The model uses an in-memory cache to store and query data. The data set is already the same size as the available RAM on the server. Data volumes are likely to continue to increase rapidly.
Your data model contains multiple calculated tables.
The data model must begin processing each day at 2:00 and processing should be complete by 4:00 the same day. You observe that the data processing operation often does not complete before 7:00. This is adversely affecting team members.
You need to improve the performance.
Solution: Change the storage mode for the data model to DirectQuery.
Does the solution meet the goal?
- A. Yes
- B. No
Answer : A
Explanation:
By default, tabular models use an in-memory cache to store and query data. When tabular models query data residing in-memory, even complex queries can be incredibly fast.
However, there are some limitations to using cached data. Namely, large data sets can exceed available memory, and data freshness requirements can be difficult if not impossible to achieve on a regular processing schedule.
DirectQuery overcomes these limitations while also leveraging RDBMS features making query execution more efficient.
With DirectQuery: +
Data is up-to-date, and there is no extra management overhead of having to maintain a separate copy of the data (in the in-memory cache). Changes to the underlying source data can be immediately reflected in queries against the data model.
Datasets can be larger than the memory capacity of an Analysis Services server.
Etc.
References:https://docs.microsoft.com/en-us/sql/analysis-services/tabular- models/directquery-mode-ssas-tabular
Question 4
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You have a Microsoft SQL Server Analysis Services (SSAS) multidimensional database that stores customer and order data for customers in the United States only. The database contains the following objects:
You must create a KPI named Large Sales Target that uses the Traffic Light indicator to display status. The KPI must contain:
You need to create the KPI.
Solution: You set the value of the Status expression to:
Does the solution meet the goal?
- A. Yes
- B. No
Answer : A
Question 5
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a Microsoft SQL Server Analysis Services (SSAS) instance that is configured to use multidimensional mode. You create the following cube:
You need to create a new dimension that allows users to list shipments by the country where the product is shipped.
Which relationship type should you use between the Shipment table and the new dimension?
- A. no relationship
- B. regular
- C. fact
- D. referenced
- E. many-to-many
- F. data mining
Answer : E
Explanation:
Many to Many Dimension Relationships.
In most dimensions, each fact joins to one and only one dimension member, and a single dimension member can be associated with multiple facts. In relational database terminology, this is referred to as a one-to-many relationship. However, it is frequently useful to join a single fact to multiple dimension members. For example, a bank customer might have multiple accounts (checking, saving, credit card, and investment accounts), and an account can also have joint or multiple owners. The Customer dimension constructed from such relationships would then have multiple members that relate to a single account transaction.
References:https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional- models-olap-logical-cube-objects/dimension-relationships
Question 6
You are a business analyst for a retail company that uses a Microsoft SQL Server Analysis
Services (SSAS) multidimensional database to track sales. The database contains the following objects:
Your company is developing a promotional plaque to recognize the top resellers in the top
10 cities where the company does business. Each plaque must display the sales total for all resellers in the city. In addition, the plaque must display a total for all cities not in the top
10.
You have the following requirements:
You need to provide the information needed for the promotional plaques.
How should you complete the MDX statement? To answer, drag the appropriate MDX segments to the correct locations. Each MDX segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Answer : 
Explanation:
Box 1:DYNAMIC SET -
Box 2:MEMBER -
Box 3:DYNAMIC SET -
Box 4:[Geography].[City].[City].members
Box 5:[Measures].[Reseller Sales Amount]
References: https://docs.microsoft.com/en-us/sql/mdx/aggregate-mdx
Question 7
You are responsible for installing new database server instances.
You must install Microsoft SQL Server Analysis Services (SSAS) to support deployment of the following projects. You develop both projects by using SQL Server Data Tools.
You need to install the appropriate services to support both projects.
Which two actions should you perform? Each correct answer presents part of the solution.
- A. Install one tabular instance of SSAS and enable the Data Mining Extensions.
- B. Install one multidimensional instance of SSAS.
- C. Install one tabular instance of SSAS.
- D. Install a multidimensional instance and a Power Pivot instance of SSAS on the same server.
- E. Install two separate tabular instances of SSAS.
Answer : BC
Explanation:
Analysis Services can be installed in one of three server modes: Multidimensional and Data
Mining (default), Power Pivot for SharePoint, and Tabular.
References:https://docs.microsoft.com/en-us/sql/analysis-services/comparing-tabular-and- multidimensional-solutions-ssas
Question 8
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
A company has an e-commerce website. When a customer places an order, information about the transaction is inserted into tables in a Microsoft SQL Server relational database named OLTP1. The company has a SQL Server Analysis Services (SSAS) instance that is configured to use Tabular mode. SSAS uses data from OLTP1 to populate a data model.
Sales analysts build reports based on the SSAS model. Reports must be able to access data as soon as it is available in the relational database.
You need to configure and deploy an Analysis Services project to the Analysis Services instance that allows near real-time data source access.
Solution: In the Deployment Option property for the report, you set the Query Mode to
InMemory with DirectQuery.
Does the solution meet the goal?
- A. Yes
- B. No
Answer : B
Explanation:
With InMemory with DirectQuery: Queries use the cache by default, unless otherwise specified in the connection string from the client.
References: https://msdn.microsoft.com/en-us/library/hh230898(v=sql.120).aspx
Question 9
You are a business analyst for a company that uses a Microsoft SQL Server Analysis
Services (SSAS) tabular database for reporting. The database model contains the following tables:
You have been asked to write a query for a report that returns the total sales for each product subcategory, as well as for each product category.
You need to write the query to return the data for the report.
How should you complete the DAX statement? To answer, drag the appropriate DAX segment to the correct locations. Each DAX segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Answer : 
Explanation:
Box 1:EVALUATE -
Box 2:SUMMERIZE -
Box 3:ROLLUP -
Box 4:'Product Subcategory' ['Product Subcategory Name]
Note: The behavior of SUMMARIZE is similar to the GROUP BY syntax of a SELECT statement in SQL. For example, consider the following query.
EVALUATE -
SUMMARIZE(
'Internet Sales',
'Internet Sales'[Order Date],
"Sales Amount", SUM( 'Internet Sales'[Sales Amount] )
)
This query calculates the total of Sales Amount for each date in which there is at least one order, producing this result.
References:
Question 10
You are writing a MDX query to retrieve data from a Microsoft SQL Server Analysis
Services (SSAS) cube named Channel Sales. The cube defines two measures named
Sales and Cost. The cube also defines a Date dimension and a Product dimension.
You need to retrieve profit values for a year named CY2016.
How should you complete the MDX statement? To answer, drag the appropriate MDX segment to the correct locations. Each MDX segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Answer : 
Explanation:
Box 1:WITH MEMBER -
Box 2:WHERE ([Date].[Year].[CY2016])
References: https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional- models/mdx/working-with-members-tuples-and-sets-mdx
Question 11
A company has a multidimensional cube that is used for analyzing sales data. You add a new measure named Transaction Total Including Tax and include the Supplier, Payment
Method, and Transaction Type dimensions in the data model. The Transaction Total
Including Tax measure uses the existing Customer and Date dimensions.
When users have queried the new measure in the past, they saw results as shown in the existing query output exhibit. (Click the Exhibit button.)
The overall total is incorrectly displayed on every row. In addition, the results are no longer formatted correctly.
The query result should appear as shown in the desired query output exhibit. (Click the
Exhibit button.)
You need to ensure the table is displayed correctly.
What should you do? Use drop-down menus to select the answer choice that answers each question based on the information presented in the graphic.
NOTE: Each correct selection is worth one point.
Answer : 
Explanation:
Box 1: Enter a custom MeasureExpression property on the measure
Calculated measures use MDX expressions to supply their values, instead of binding to columns in a data source. The Expression property contains the MDX expression used to supply the values for a Measure only if the Measure is a calculated measure. Otherwise, this property contains an empty string ("").
Question 12
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a Microsoft SQL Server Analysis Services (SSAS) instance that is configured to use multidimensional mode. You create the following cube:
Users need to be able to analyze sales by product and color.
You need to create the dimension.
Which relationship type should you use between the InternetSales table and the new dimension?
- A. no relationship
- B. regular
- C. fact
- D. referenced
- E. many-to-many
- F. data mining
Answer : D
Explanation:
A reference dimension relationship between a cube dimension and a measure group exists when the key column for the dimension is joined indirectly to the fact table through a key in another dimension table, as shown in the following illustration.
A reference dimension relationship represents the relationship between dimension tables and a fact table in a snowflake schema design. When dimension tables are connected in a snowflake schema, you can define a single dimension using columns from multiple tables, or you can define separate dimensions based on the separate dimension tables and then define a link between them using the reference dimension relationship setting. The following figure shows one fact table named InternetSales, and two dimension tables called
Customer and Geography, in a snowflake schema.
You can create two dimensions related to the InternetSales measure group: a dimension based on the Customer table, and a dimension based on the Geography table. You can then relate the Geography dimension to the InternetSales measure group using a reference dimension relationship using the Customer dimension.
Question 13
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
A company has an e-commerce website. When a customer places an order, information about the transaction is inserted into tables in a Microsoft SQL Server relational database named OLTP1. The company has a SQL Server Analysis Services (SSAS) instance that is configured to use Tabular mode. SSAS uses data from OLTP1 to populate a data model.
Sales analysts build reports based on the SSAS model. Reports must be able to access data as soon as it is available in the relational database.
You need to configure and deploy an Analysis Services project to the Analysis Services instance that allows near real-time data source access.
Solution: In the Deployment Option property for the report, you set the Query Mode to
DirectQuery with InMemory.
Does the solution meet the goal?
- A. Yes
- B. No
Answer : A
Explanation:
With DirectQuerywithInMemory mode the queries use the relational data source by default, unless otherwise specified in the connection string from the client.
References:https://msdn.microsoft.com/en-us/library/hh230898(v=sql.120).aspx
Question 14
You have a Microsoft SQL Server Analysis Services (SSAS) multidimensional project. You are developing a dimension that uses data from the following table:
The ManagerKey column defines a foreign key constraint that references the EmployeeKey column. The table stores employee history information by using slowly changing dimensions (SCD). Changes to EmployeeName, Phone, or ManagerKey are managed as
SCD Type 1 changes. Changes to SalesRegion are managed as SCD Type 2 changes.
You create the following attributes, and set the KeyColumns and NameColumn properties to the columns listed in the table below:
You need to add a parent-child hierarchy to the dimension to enable navigating the organization hierarchy.
In the table below, identify the attribute that you must use for each attribute usage type.
NOTE: Make only one selection in each column.
Answer : 
Explanation:
The ManagerKey column, the Manager attribute, defines a foreign key constraint that references the EmployeeKey column, the Employee attribute.
Question 15
You are optimizing a Microsoft SQL Server Analysis Services (SSAS) multidimensional model over a SQL Server database. You have a table named City which has several dimensions that do not contain a space in their names. One dimension is named
SalesTerritory rather than Sales Territory.
You need to ensure that Report developers can drag the attribute name to the report rather than having to re-label the attributes by implementing spaces. You must minimize administrative effort and not break any upstream processes.
What should you do?
- A. In the SQL Server database, run the system procedure sp_rename to rename the columns in the base tables with the target name.
- B. In SQL Server Management Studio, navigate to the City table, expand the columns, press F2, and rename the columns in the base tables.
- C. In the SQL Server database, implement a SYNONYM.
- D. In the SQL Server database, implement a view over the City table that aliases the columns in the tables.
Answer : D