Considerations and Comments
Because
setup of the environment to prepare for a hands-on walkthrough of the steps
would be time consuming, we will discuss the steps, and I will provide
illustrations that I hope will make an approach I have used clearer. There are,
of course, many ways to accomplish the same ends, and we can leverage numerous
tools for this purpose, as well. The important thing is to gain an awareness of
the concepts, the chief of which is the provision of a means for supplying MDX
qualified names to Reporting Services so that it can complete and
execute an MDX query to create a Dataset based upon a selected Analysis
Services cube.
We will be working in MSSQL
Server to create the picklist support table. Moreover, we will use Reporting
Services to create the picklist Dataset, as well as the primary Dataset
to which we supply the parameters that are selected by the
information consumer at runtime. We would likely have Analysis Services
open in the background, to refer to the cube structure as we construct the MDX
that populates the picklist support table. In addition, we might use
the MDX Sample Application (or another query tool) to formulate and test
the MDX syntax that we ultimately put into our picklist support table.
Whatever the means, one approach to meeting the expressed or similar consumer
needs follows in the next section.
Procedure
Preparation
The
first objective is to create a table to contain the syntax that will
ultimately populate the Value and Label fields of the Report
Parameters dialog inside the Report Designer of the intended report
within Reporting Services.
Create
a Relational Table to Contain the Picklist Items
To begin
we would create a table to contain the selections that we wish to appear in the
parameter picklist at runtime, together with the associated "qualified"
MDX name (the Unique Name) that is needed in creating a Dataset
based upon an Analysis Services cube.
A simple
design for this table, to which I refer as MSAS_PickListMaster (I try to
name tables in the relational database in a way that makes dependencies easy to
understand, but this could obviously be named in accordance with any
convention), appears in Illustration 2.
Illustration 2: A Simple
Table Design for Parameter Picklist Support
The
columns I have defined in MSAS_PickListMaster represent but a few of
many possibilities. Their purposes are detailed in Table 1.
Column
|
Contains:
|
PL_Code
|
A high-level grouping ("Picklist
Code") to assist in the selection of the correct picklist items for a
given picklist Dataset in Reporting Services.
(Examples: Product, Store, etc.)
|
NameType
|
A
subgrouping within PL Code, whereby we might provide even more precise
filtering in our picklist Dataset query.
(Examples:
Product Family, Store Country, etc.)
Note
that we may create additional subgroup columns, as well, for similar purposes
at varying levels, etc.
|
DispName
|
The
picklist selection name we wish to display to the information consumer
at report execution time.
|
UniqName
|
The "qualified"
MDX name, known as the Unique Name within MDX, and corresponding to
the Display Name that the consumer selects.
|
Sort1
|
One of
several possible sorts, I included this one to allow for "other than
alpha" sorting of the parameter selections within the Dataset
queries that generate the parameter picklists.
|
Table 1: Columns of the Parameter Picklist Support Table
The populated table
(using an MSSQL Server table as an example) appears as partially shown in Illustration
3.
Illustration 3: Partial
View of the Populated Parameter Picklist Support Table
NOTE: In creating a table that we actually use to support
picklists within a modification of the sample FoodMart Sales report, it
would likely make more sense to create the parameter picklist support
table within MS Access, where the relational tables supporting the FoodMart
Sales cube (which contains the data presented in the report) reside. While
perhaps not a hard and fast requirement, placing the new table in the primary
source database would certainly mean more convenient reporting and upkeep.
Virtually all my real-world cube data sources rest upon
the MSSQL Server RDBMS, (even when the ultimate OLTP(s) involved lie within
other RDBMS'). A great deal of effort must often be expended to build fully
functional training examples in MS Access. This is one of the reasons that I
have avoided doing so in this article, as the preparation alone would dwarf the
procedure involved in the focus of the article, the use of a relational table
to support parameter picklists in OLAP reports.
Having
created the table, based upon expressions that we might assemble and test using
the MDX Sample Application (or other OLAP query tool), we can now move
to Reporting Services and create the Dataset upon which we will
base our parameter picklists. We will examine the general steps in the
section that follows.
Create a Picklist Dataset to House the Picklist Data
Our next steps
will be to open the Visual Studio project that houses the report (or to create
a new project, obviously, if we are building the report from scratch), and then
to open (or create) the report involved. In our example of enhancing the FoodMart
Sales report, we would begin by creating a Dataset to support each
of the report parameters that we wished to create.
To
illustrate, let's look at an example for Product Family (expecting that
we will have discarded the Product Family parameter that came with the
sample report). We would first create a data source specifying the
database within which we have housed our new picklist support table. Then
we would create the SQL query to produce the Dataset to which we
would refer in our subsequent creation of the corresponding Report Parameter
for Product Family.
The query
itself would look something like the following:
SELECT DispName, UniqName
FROM MSAS_PickListMaster
WHERE (PL_Code='PROD' AND NameType='Family')
ORDER BY Sort1
The logic
behind this simple query is probably obvious to most of us. We are creating a straightforward
list (much as I described in detail, within an MDX context in String Functions: The .UniqueName
Function, in the MDX Essentials series)
whereby we generate two columns. The first houses the Display
Name for the picklist selection, while the second houses the Unique
Name, the MDX syntax that must be passed to the MDX query that
generates the ultimate Dataset (from the
cube) upon which the FoodMart Sales report is
based. Because we are interested in Products in general,
and Product Families specifically, we would insert these
specifications in the WHERE clause, as
shown above. Moreover, our provision of the sort key in the ORDER BY clause
would produce a Dataset that sorts
in the order we wish for it to assume in the ultimate picklist. (This could
also be done in the report itself, among other places; optimization of the
report in general requires consideration of where to place many of the
processes to get the most bang for our processing buck). This would allow us to
meet the need for the "default" selection of "All
Families" to appear at the top of the list, and therefore as default, in the parameter
picklist selector, with no additional user interaction at run time.
Once we had clicked the Run button in
the Report Designer, our efforts would be rewarded with
a Dataset that resembles that partially
depicted in Illustration 4.
Illustration 4: The
Picklist Support Dataset Appears ...
We can
hone in on the logic we have used to meet the requirements of the consumers in Table
2, which presents the Dataset for the Product Family
parameter picklist.
DispName
|
UniqName
|
All
Families
|
Descendants([Product].[All
Products], [Product].[Product Family])
|
Food
& Drink
|
[Product].[All
Products].[Food], [Product].[All Products].[Drink]
|
Food
|
Descendants([Product].[Food],
[Product].[Product Family])
|
Drink
|
Descendants([Product].[Drink],
[Product].[Product Family])
|
Non-Consumable
|
Descendants([Product].[Non-Consumable],
[Product].[Product Family])
|
Table 2: Close-up View of the Data in the Dual Columns of the Dataset
Note how
the "All" capability is supported by the appropriate MDX (we would,
again, have tested all our Unique Name syntax independently before
creating a Dataset based upon it to support our picklists). Moreover,
note how we are able to create a "grouping" of the Food and
Drink families, within its own picklist line item. This might have been
done numerous ways (I often create Named Sets within the cube to house
specifications of this type, and then simply place the Named Set titles
in the Unique Name column of the table, as applicable, among other
approaches). Suffice it to say that much flexibility exists in our design
choices, allowing us to optimize performance of the integrated solution in ways
that can be highly customized to our individual business environments.
One of
the greatest advantages in using the relational table to support the OLAP
report picklists is that we can add new picklist items easily in one central
location that might lie outside the hierarchical structure of our cube
design, such as we have done with the Food & Drink item. We can
also make name changes for picklist Display Names (and for that matter
report Display Names) here, as well a handy capability if the need
arises to change the presentation in the report of an item whose name we wish
to leave in its original format in the OLAP data source itself. This can
become particularly useful if the report creator needs to "restate"
member names for, say, members of a given dimensional level, but has no editor
capabilities within the Analysis Services cube. Many other advantages
become apparent in "real world" situations where the use of the
relational picklist table, or a hybrid thereof, can be seen as a viable option.
We will
not belabor the point with additional Datasets, as the same logic would
apply, in our example, at lower levels of the Product dimension. Next,
let's move into the creation of an example Report Parameter, referencing
our new Product Family picklist Dataset as part of the process.