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 (https://blog.sqlauthority.com)
3 Comments. Leave new
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
Your explanation of attribute is wrong !
Hi,
Please provide the example of Cube in ssas.