Microsoft Office Tutorials and References
In Depth Information
SSAS is used to build end-to-end analysis solutions for enterprise data. Because of its high performance and
scalability, SSAS is widely used across organizations to scale their data cubes and tame their data warehouse models.
Its rich tools give developers the power to perform various drill-down options over the data.
Let’s take care of the easy part first. One way to get started with cubes is by using the samples provided with the
AdventureWorks database. You can download the samples from the msftdbprodsamples.codeplex.com/downloads/
get/258486 link. The download has two projects for both the Enterprise and Standard versions:
Open one of the folders based on your server license, and then open the AdventureWorks
DW2012Multidimensional-EE.sln (for Enterprise Edition) file using SSDT.
Open the Project Properties and set the Deployment Target Server property to your SQL
Server Analysis Services Instance.
Build and deploy the solution. If you get errors during deployment, use SQL Server
Management Studio to check the database permissions. The account you specified for
the data source connection must have a login on the SQL Server instance. Double-click
the login to view the User Mapping properties. The account must have db_datareader
permissions on the AdventureWorksDW2012 database.
This project contains lots of other resources, including data sources, views, cubes, and dimensions. However,
these provide information relating to the layout, structure, properties, and so forth of SSDT, and they contain little
information about cube structures themselves.
Once the project is deployed successfully (with no errors), open SSMS. You’ll find the cubes from the previous
deployment available for access via MDX queries. Later in this chapter, you’ll learn how to create a simple cube. For
now, we’ll use the available AdventureWorks cubes.
Executing an Analysis Services MDX query from SSMS involves few steps:
Run SQL Server Management Studio, and connect to the Analysis Services instance on
your server by choosing Analysis Services under the Server type.
Click the New Query button, which launches the MDX Query Editor window.
In the MDX Query Editor window, from the Cube drop-down menu, select one
of the available cubes from the Analysis Services database you’re connected to,
“AdventureWorks” in this case.
Under the Metadata tab, you can view the various measures and dimensions available for
the cube you selected.
Alternatively, you can click on the Analysis Services MDX Query icon at the top to open the
Writing an MDX query is similar to writing a T-SQL query. However, instead of querying
from tables, MDX queries from a cube’s measures and dimensions.
Enter your MDX query in the query window, and execute to view the output, as shown
in Figure 1-35 .
Search JabSto ::