Performance Tuning and Troubleshooting Tabular Models - SQL Server Analysis Services 2012 Cube Development - page 407

Now we'll do some serious performance testing. To set this up, we need a calculation
added to the model that demands more resources. Depending on how a query is
constructed, the following calculation might take several seconds to run. Keep in
mind that our Tabular database is quite small, but if this measure is used with several
million rows of data loaded into the model, it could take several minutes to run:
1. Add the following measure to a cell in the measures grid of the Internet
Sales table. Don't enter any carriage returns; the text wraps to fit on the prin-
ted page of the topic.
Qty Ratio Over Reorder
Point:=DIVIDE([Order Quantity],
sum(Product[ReorderPoint]))
2. Press Enter to validate and create the measure. Now, move on to another
cell and enter the following calculation all on one line:
Last Order Qty Ratio Over Reorder
Point:=sumx(values('Internet
Sales'[ProductKey]), CALCULATE([Qty Ratio
Over Reorder Point],
LASTNONBLANK('Internet Sales'[OrderDate],
[Qty Ratio Over Reorder Point])))
3. Again, press Enter to validate and save the measure.
4. Deploy the database to the SSAS Tabular instance like you did in Chapter
10 , DAX Calculations and Queries .
5. Now we're ready to query the model and use the new measure. Open Excel
to create a new workbook.
6. On the DATA tab, select the From Other Sources icon in the Get External
Data group.
7. Select From Analysis Services . Enter the server and instance name and
click on the Next button. Select the database name. Click on Next and then
click on Finish to complete the connection.
8. When prompted, add a PivotTable report.
9. Right-click on CalendarYear and select Add as Slicer .
10. In the slicer, click on select 2005 .
11. From the Date table, expand More Fields .
12. Select the FullDate field to add it to ROWS .