Hi,
I have a design issue and not sure, which approach to follow. Iam not sure, if I have to use analysis services or integration services. But we would like to use price trending also, so probably analysis services could be an option.
We have a table which stores the Customer Items information. For making it easier, I will talk about customer 1 and item 1as shown in the table below:
CustomerID
CustomerCategoryID
RegionId
ItemID
Price
1
1
2
1
10.00
2
1
2
1
12.00
3
1
2
1
14.00
4
2
1
1
18.00
5
2
1
1
16.00
6
3
3
1
15.00
7
4
3
1
12.00
Now the Customers can belong to a single Category and single Region
Customer Categories are 6 (1 -6) and Category 7 indicates all Categories.
Regions are 6 (1-6) and Region 7 indicates all Regions.
Now the reports run from the following particular cube (under the assumption that analysis services will be used to build the following cube)
CustomerID
CustomerCategoryID
RegionID
ItemID
Price
LowPrice
AveragePrice
1
1
2
1
10.00
10.00
12.00
2
1
2
1
12.00
10.00
12.00
3
1
2
1
14.00
10.00
12.00
1
2
1
1
10.00
16.00
17.00
1
3
3
1
10.00
15.00
15.00
1
4
3
1
10.00
12.00
12.00
1
7
1
1
10.00
16.00
17.00
1
7
2
1
10.00
10.00
12.00
1
7
3
1
10.00
12.00
13.50
1
7
4
1
10.00
NULL
NULL
1
7
5
1
10.00
NULL
NULL
1
7
6
1
10.00
NULL
NULL
1
1
7
1
10.00
10.00
12.00
1
2
7
1
10.00
16.00
17.00
1
3
7
1
10.00
15.00
15.00
1
4
7
1
10.00
12.00
12.00
1
5
7
1
10.00
NULL
NULL
1
6
7
1
10.00
NULL
NULL
As you can see in the above table for customer 1 and item 1, the lowest and average price is calculated for every available combination for categoryid and regionid(the ones in red indicate all regions and all categories). This may not be the best example, but hope it will help in understanding the issue here. What is the best approach to design this one ? If I have to design the cube can anybody help me in getting started. I have a basic understanding of MDX queries and trying to get a hang of it.
Thanks in advance for your help.
-Prash
Hi,
This is the general dimension model and analysis server 2005 can take care of this very easily.
If you have seperate tables for the Customers, CustomerCategory, Region and Items, then start of by modelling these as dimensions in analysis server. The fact table which you have shown would have foreign key references to the dimenison tables.
If you dont have dimension tables, you can create them. Customers and CustomersCategory can be attributes of the same dimension.
You will have Price, LowPrice and AveragePrice as your measures in the cube.
start of by creating a DataDource, then a DataSourceView. Once this is ready, click on new cube, then select your fact and dimension tables, select your measures and click finish.
The DSV (DataSourceView) would identify the fact and dimension tables. you would need to create primary keys and then create relationships from the fact table to the dimension tables. (for customers, region and item tables)
Hope this is what you were looking for
Regards
|||Thanks for the reply. I did go ahead and build the cube with Price, LowPrice and AveragePrice as measure.
But Iam having problem with the query below for building the above mentioned report. Any help on this would be really appreciated.
Select
{
[Measures].[Price]
,[Measures].[MinimumPrice]
,[Measures].[AveragePrice]
} on Columns,
{
(
Filter
(
NONEMPTYCrossJoin
([DimRegions].[DimRegions].Children,
[DimCustomerCategories].[DimCustomerCategories].Children,
[DimItem].[ItemName].Children,
[DimCustomer].[CustomerName].Children
)
,[Measures].[Count No Of Rows] > 1
)
)
} on Rows
From [Cube1]
