Database Reference
In-Depth Information
Problems with TinyInt
Unfortunately, there's a bug in Analysis Services that causes a problem in
the DSV when you use key columns of the
tinyint
type. Since Analysis
Services doesn't support this type natively, the DSV attempts to convert
it to something else—a
System.Byte
for foreign keys to dimensions
on fact tables and
System.Int32
for primary keys on dimension tables
which have Identity set to
True
. This in turn means you can no longer
create joins between your fact table and dimension table. To work around
this, you need to create a named query on top of your dimension table
containing an expression that explicitly casts your tinyint column to a
tinyint (for example, using an expression such as
cast(mytinyintcol
as tinyint)
), which will make the DSV show the column as
System.
Byte
. It sounds crazy, but for some reason it works.
Designing simple dimensions
Next, let's build some dimensions. As this is one of the more complicated steps in the
cube design process, it's a topic we'll return to again in the future chapters when we
need to deal with more advanced modeling scenarios. Right now, we'll concentrate
on the fundamentals of dimension design.
Using the New Dimension wizard
Running the
New
Dimension
wizard will give you the first draft of your
dimension, something you'll then be able to tweak and tidy up in the Dimension
Editor afterwards. The first question you'll be asked, in the
Select Creation Method
step is how you want to create the new dimension and there are effectively the
following two choices:
• Create the dimension from an existing table or view in your data source
(the
Use an existing table
option)
• Have SSDT create a dimension automatically for you and optionally fill it
with data (the other three options)
Search WWH ::
Custom Search