Online Access Free 070-470 Practice Test

DRAG DROP
You are designing a business intelligence (BI) solution for a hotel booking system. Each hotel booking may have more than one guest.
The BI solution contains a fact table named Booking, a factless fact table named Booking_Guest and a dimension named Guest.
Booking contains an entry for each booking. Booking_Guest contains an entry for each guest in each booking. Guest contains an entry for each guest.
You need to define the many-to-many relationship in SQL Server Analysis Services (SSAS) for the hotel booking system.
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Correct Answer:

DRAG DROP
You have a business intelligence (BI) infrastructure that contains three servers. The servers are configured as shown in the following table.

You need to recommend a health monitoring solution for the BI infrastructure.
The solution must meet the following requirements:
Monitor the status of the Usage Data Collection feature.
Monitor the number of end-users accessing the solution.
Monitor the amount of cache used when the users query data.
Which health monitoring solution should you recommend using on each server? To answer, drag the appropriate monitoring solutions to the correct servers. Each monitoring solution 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.
---

Correct Answer:

You execute the SalesbyCategory report and receive the following error message:
"Members, tuples, or sets must use the same hierarchies in the function."
You need to ensure that the query executes successfully.
Which two actions should you perform? Each correct answer presents part of the solution.

• A.Move the Date clause from line 10 to line 08.
• B.Move the Measures clause on line 02 to axis 1.
• C.Move the Product clause from line 08 to line 10.
• D.Move the Date and Product clauses on line 11 to axis 0.

You are developing a SQL Server Analysis Services (SSAS) cube.
The data warehouse has a table named FactStock that is used to track movements of stock. A column named MovementQuantity contains quantities of stock. A positive quantity is used for input and negative quantity is used for output. A column named MovementDate is related to the time dimension. The quantity in stock, at a given point in time, can be evaluated as the sum of all MovementQuantity values at that point in time.
You need to create a measure that calculates the quantity in stock value.
What should you do?

• A.Create a dimension. Then add a cube dimension and link it several times to the measure group.
• B.Add a measure that uses the DistinctCount aggregate function to an existing measure group.
• C.Create a dimension with one attribute hierarchy. Set the ValueColumn property, set the IsAggregatable property to False, and then set the DefaultMember property. Configure the cube dimension so that it does not have a relationship with the measure group. Add a calculated measure that uses the MemberValue attribute property.
• D.Create several dimensions. Add each dimension to the cube.
• E.Create a dimension. Create regular relationships between the cube dimension and the measure group. Configure the relationships to use different dimension attributes.
• F.Add a measure that uses the Count aggregate function to an existing measure group.
• G.Add a measure that uses the LastNonEmpty aggregate function. Use a regular relationship between the time dimension and the measure group.
• H.Create a new named calculation in the data source view to calculate a rolling sum. Add a measure that uses the Max aggregate function based on the named calculation.
• I.Add a measure group that has one measure that uses the DistinctCount aggregate function.
• J.Create a dimension with one attribute hierarchy. Set the IsAggregatable property to False and then set the DefaultMember property. Use a regular relationship between the dimension and measure group.
• K.Add a hidden measure that uses the Sum aggregate function. Add a calculated measure aggregating the measure along the time dimension.
• L.Create a dimension with one attribute hierarchy. Set the IsAggregatable property to False and then set the DefaultMember property. Use a many-to-many relationship to link the dimension to the measure group.
• M.Use role playing dimensions.
• N.Use the Business Intelligence Wizard to define dimension intelligence.
• O.Add a calculated measure based on an expression that counts members filtered by the Exists and NonEmpty functions.

You are developing a BI Semantic Model (BISM) that retrieves data from several sources. These sources include a SQL Azure database and an OData data feed that includes rainfall data for towns on the east coast of Australia. The model will be deployed to a server with significantly more memory than the total size of the source data.
You have the data feed URL, which you will use when developing the model in SQL Server Data Tools (SSDT). The model must meet the following requirements:
- Performance must be maximized.
- Data latency of up to one month is acceptable.
You need to choose a project type and a data access mode to meet the requirements.
What should you do?

• A.In SSDT, select the multidimensional project type and use the MOLAP storage mode.
• B.In SSDT, select the tabular project type and use the In-Memory query mode.
• C.In SSDT, select the multidimensional project type and use the ROLAP storage mode.
• D.In SSDT, select the tabular project type and use the DirectQuery query mode.