Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-08-02 : 11:02:44
|
Hema writes "Here is the problem i am facing
I have the following tables
Table : Orders (OrderId , Email, OrderDate, ShipDate, Status, .....) Primary key: OrderId
Table: OrderPricing (OrderId, TotalOrderPrice, DiscountCode,
DiscountPrice, ....)
Table: Users (EmailId, UserName, address, UserType,.....etc)
Relationship Between the above tables
Orders ----1 to 1 --> OrdersPricing ( Orders.OrderId = OrdersPricing.OrderId)
Orders --- 1 to Many --> OrderItems (Orders.OrderId= Orderitems.Orderid)
Users --- 1 to Many --> Orders (Email Column)
Order.Email = Users.Email
The above tables have more than millions of transaction records over years.
I have a created a fact table through view using Orders and OrderPricing tables
The following is the view
Orders.OrderId,
Orders.Email,
Orders.PartnerId,
Orders.Send_Date,
Orders.Date_Ordered,
OrdersPricing.Total_Price,
OrdersPricing.Discount_Code,
OrdersPricing.SourceId
HERE IS THE FORMAT OF THE REPORT I HAVE TO CREATE
Report: Total Sales/ Average Sales Report by quarter by user
Where Total Sales => Total_Price column from Orderspricing
Average Sales => Calculated Measure in the dimension.
-------------------------------------------------------
User | 2001 2002 ....
|Q1 | Q2 | Q3 | Q4 |Q1 | Q2 | Q3 | Q4 |
----------------------------------------------------------
abc@email.com |993|93838|9393|343|343|343|6333| 3433|...
someone@email.com |433|24342|9393|542|343|343|6333| 3433|...
test@email.com |452|94435|3493|373|343|343|6333| 3433|...
...
...
..
I am very new to this field and I am not sure how to proceed further to create the above drill down report and time will be one of the dimension
Thanks in advance
Hema
hemalashmi@yahoo.com" |
|
|
|
|
|
Need help in desiging dimensions and cube