Database Reference
In-Depth Information
Final Code Cleanup
As you can see, the code works as expected. But it contains a lot of extra, unnecessary code. This extra
code, such as the
foreground color
, specifies settings to be used in client applications. standard Microsoft
client applications ignore these settings. Therefore, they are not necessary if you are using your MDX code to
create reports in ssRs or Excel.
1.
Reformat the code to exclude all client properties added by the designer. Your new
code should look like Listing 14-43.
Listing 14-43.
Reformatting the Automatically Generated MDX Code
SELECT
NON EMPTY { [Measures].[SalesQuantity] } ON COLUMNS
, NON EMPTY { (
[DimDates].[Year].[Year].ALLMEMBERS
*
[DimTitles].[Title].[Title].ALLMEMBERS
) }
ON ROWS
FROM [CubePubsSales]
2.
Highlight the select statement within your reformatted code and execute the query.
3.
Verify that your results still match the results shown in Figure
14-36
.
in this exercise, you generated MDX code using the cube browser, copied the code to a new MDX code
window, and reformatted and tested the code. while there is still much to learn about MDX, we hope you can
now read and work with the code generated by the designer!
Moving On
At this point, you should be able to write and read basic MDX queries. While there is always more to learn, the
good news is that most developers can work for many years in the BI field with the basic MDX knowledge covered
in this chapter.
You may ask, “Then why should I learn this at all?” The answer is simple; MDX code created by applications
is rarely the most efficient code to use. And sometimes the code generated by applications is incorrect for what
you need to accomplish.
In addition, your MDX skills are a powerful tool for troubleshooting. Finally, you will need to use MDX
expressions in many places throughout the SSAS management and development tools. While you will seldom
use whole queries in these places, understating how MDX expressions work within an MDX query will help you
create and troubleshoot there as well.
learn bY Doing
in this “Learn by Doing” exercise, you create several MDX queries using the northwind database. we
included an outline of the steps to perform and an example of how the authors handled them in two word
documents. These documents are found in the folder
C:\_BISolutionsBookFiles\_LearnByDoing\
Chapter14Files
. Please see the
ReadMe.doc
file for detailed instructions.
