Databases Reference
In-Depth Information
Figure 7-3
When a user accesses the cube, the fact data first gets loaded into the cube
as shown in
Figure 7-1
;
which represents data for a specific year. Depending
on the storage type (ROLAP, MOLAP, or HOLAP) the fact data would be re-
trieved from the relational data source or from local Analysis Services stor-
age. This is referred to as PASS 0 within Analysis Services. Consider PASS
as an analogy of doing a first visit of all the cells within the cube. Once the
fact data has been loaded into the cube, Analysis Services applies calcula-
tions for the cells based on the calculations specified in the MDX scripts or di-
mensions attributes. Assume this cube has the default MDX script with a
CALCULATE command. After loading the fact data, Analysis Services ex-
ecutes the MDX script. When the CALCULATE statement is encountered, the
fact data aggregated for appropriate levels of the dimension hierarchies is
made accessible to end users. Because the Product Line and Customer Geo-
graphy hierarchies have only one level, there is no need to aggregate the
data. The Date hierarchy has the levels Semester and Year for which the data
needs to be aggregated from the Quarter level. When the CALCULATE state-
ment is encountered a new PASS; PASS 1, is created where aggregated data
can be seen for various levels. Analysis Services aggregates the data for the
Semester level from the Quarter level as shown in
Figure 7-2
and then to the
Year level as shown in
Figure 7-3
.
You will be able to query the aggregated
data.
If the CALCULATE statement is not specified in the MDX script, you will not
be able to query the aggregated data for Semester and Year. If you query the
data for the levels Semester and Year you will get null values. A concern
about missing a CALCULATE statement in the MDX script is that you can re-
trieve the fact level data only when you include all hierarchies of all dimen-
sions in your query. For example, if you have the following query:
SELECT [Measures].[Internet Sales Amount] on COLUMNS,
Product.[Product Line].Members on ROWS
FROM [Adventure Works]
You will only see null values. In order to retrieve the fact data, you would need
to send the following query to Analysis Services:
SELECT [Measures].[Internet Sales] on COLUMNS,
Product.ProductLine.Members *
Customer.[Customer Geography].members *
Search WWH ::
Custom Search