Databases Reference
In-Depth Information
Figure 16-24
In the above data load example there was one-to-one mapping between the
columns in the flat file and the measures and granularity attributes of the par-
titions, except for the reference dimension granularity attribute DimGeo-
graphy. This was possible because all the measures in the partition directly
mapped to the columns in the flat file. Assume you have a fact data column
that was used twice in a measure group as two measures: one with sum as
the aggregation function and another as count or distinct count as aggrega-
tion functions. Such a scenario is pretty common. In this scenario you will not
be able to map the corresponding column from the flat file to the two meas-
ures since SSIS partition processing transform disallows a column from the
source (in this example flat file) to be mapped to multiple destination columns
that are part of the partition. If you ignore mappings even for a single destina-
tion column that is part of the partition, your data load will fail. You would need
to either have additional column(s) in the source so that you can map those to
the corresponding columns in the partition or use SSIS transform Copy
Column to duplicate existing column(s) to serve as input to the partition pro-
cessing transform. We recommend you modify the AdvenetureWork-
s2005Tutorial database to have a distinct count measure in Internet Sales
partition and then create an SSIS package with Copy Column transform
between flat file data source and partition processing transform to map the
column from fact file to the distinct count measure.
You have successfully learned to create SSIS packages for performing ad-
ministrative tasks on Analysis Services such as backup and processing. Oth-
er administrative tasks such as synchronization, restore, etc., can be per-
formed using the tasks and transforms provided by SSIS. In addition to
Search WWH ::
Custom Search