SQL SERVER – SSAS – Multidimensional Space Terms and Explanation

I was presenting on SQL Server session at one of the Tech Ed On Road event in India. I was asked very interesting question during ‘Stump the Speaker‘ session. I am sharing the same with all of you over here.

Question: Can you tell me in simple words what is dimension, member and other terms of multidimensional space? There is no simple example for it.

This is extreme fundamental question if you know Analysis Service. Those who have no exposure to the same and have not yet started on this subject, may find it a bit difficult. I really liked his question so I decided to answer him there as well blog about the same over here.

Answer: Here are the most important terms of multidimensional space – dimension, member, value, attribute and size.

Dimension – It describes the point of interests for analysis.

Member – It is one of the point of interests in the dimension.

Value – It uniquely describes the member.

Attribute – It is collection of multiple members.

Size – It is total numbers for any dimension.

Let us understand this further detail taking example of any space. I am going to take example of distance as a space in our example.

Dimension – Distance is a dimension for us.
Member – Kilometer – We can measure distance in Kilometer.
Value – 4 – We can measure distance in the kilometer unit and the value of the unit can be 4.
Attribute – Kilometer, Miles, Meter – The complete set of members is called attribute.
Size – 100 KM – The maximum size decided for the dimension is called size.

The same example can be also defined by using time space. Here is the example using time space.

Dimension – Time
Member – Date
Value – 25
Attribute – 1, 2, 3…31
Size – 31

I hope it is clear enough that what are various multidimensional space and its terms.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

3 thoughts on “SQL SERVER – SSAS – Multidimensional Space Terms and Explanation

  1. 1)
    CREATE NONCLUSTERED INDEX [idx_UnivStock_S_01] ON [vaapps].[tVehicle]
    (
    [COMPANYID] ASC,
    [IsDutiable] ASC,
    [IsAvailableForSale] ASC,
    [IsSold] ASC,
    [isinvoiced] ASC,
    [YearModel] ASC,
    [VehicleID] ASC
    ) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

    2)
    CREATE NONCLUSTERED INDEX [idx_UnivStock_S_02] ON [vaapps].[tVehicle]
    (
    [OwnershipLocationID] ASC,
    [COMPANYID] ASC,
    [IsDutiable] ASC,
    [IsAvailableForSale] ASC,
    [IsSold] ASC,
    [isinvoiced] ASC,
    [YearModel] ASC
    ) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
    go

    3)
    CREATE NONCLUSTERED INDEX [idx_UnivStock_S_03] ON [vaapps].[tVehicle]
    (
    [COMPANYID] ASC,
    [IsDutiable] ASC,
    [IsAvailableForSale] ASC,
    [IsSold] ASC,
    [isinvoiced] ASC,
    [YearModel] ASC,
    [VehicleID] ASC,
    [OwnershipLocationID] ASC,
    [ModelID] ASC,
    [BrandID] ASC,
    [ProductID] ASC,
    [ModelGrade] ASC,
    [Brand] ASC,
    [Product] ASC,
    [Status] ASC
    )
    INCLUDE
    (
    [SupplierModelCode],
    [CurrentLocationID],
    [ShipmentID],
    [IsRequested],
    [IsDamaged],
    [ColourCode]
    ) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

    1) Can sombody tell me what is the difference between above 3 index (script) ?
    2) if i create index based on #3 will #1 and #2 be convered in #3? if yes the how ? if not then how. pls. explain as i want to learn some more on index.
    3) is there any limit on statistics (CREATE STATISTICS [name] ON [table.name] (col1, col2, col3…..)

    regards
    Ashfaq Park

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s