Database Reference
In-Depth Information
Not
■
since stored procedures often use parameters, it seems odd that the Parameters button is not available.
This button is enabled only when there are parameters defined using the Microsoft Query add-on for Excel. This
add-on is, more often than not, unavailable on most computers. As such, we will not be including examples of how to
use this feature. As you can see in figure
15-16
, you can still provide parameter values as part of your sQl code.
Once you have clicked OK, the data in the pivot table report will change based on the results of the select
statement inside the reporting stored procedure. (You created the stored procedure by running the SQL code in
Exercise 15-1.) The results of this procedure included publisher, title, dates, and a number of measured values
including a KPI value, filtered by only titles that start with the letter
C
(Figure
15-18
).
Figure 15-18.
Reporting from a stored procedure
Working with Excel Reports from a Cube
The stored procedure and view are common ways to generate reports from a data warehouse. The stored
procedures and views can be very restricted unless all the data from all the tables in the data warehouse are
queried. The second view, vAllTables, did select data from all the tables in the data warehouse, but this is
impractical for most occasions. If the data warehouse gets very large with lots of tables, columns, and rows, your
view will be slow and cumbersome.
One of the advantages of using a cube is that all the data from the cube is easily accessible from client
software such as Excel. From Excel's perspective, it is as though all the tables have been combined into one single
object—the cube. As stated previously, you can think of a cube as a set of one or more relational tables combined.
You can really see how this analogy fits from the perspective of reporting applications.
Connecting to Your Cube
To connect to the cube, access the Data tab and select the From Other Sources button. Next, click the From
Analysis Services button to connect to SSAS (Figure
15-19
).
