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 |
|
hank755_ca
Starting Member
2 Posts |
 Posted - 2007-09-27 : 10:30:47
|
| Hello, I have a Sales cube and I want to be able to display Products and the Price at which they were purchased in a given period. I have a Product dimension while Price is a measure in my Sales Fact Table. Is there a way to have a "Group By" aggregation instead of a Sum? This way, I can show Products grouped by their list price. My Product Dimension consists of product_numbers (such as 100, 101, 102 etc...) My Sales Fact Table consists of sales data (such as product_key, price, net_sales, returns, etc...) at the transaction level. I want to be able to view the data like this: Price Net_sales Returns Product 100 $5.99 $2005 $320 101 $3.51 $7110 $998 where Net_sales and Returns are "summed" and Price is simply a "Group By". In other words, this report would show the net sales and returns by product for a given price. I'd rather not use a Price dimension since we have hundreds of products at hundreds of different prices. Moreover, this data is already in the Sales Fact table. Thanks for any help provided |
|
|
hank755_ca
Starting Member
2 Posts |
Posted - 2007-09-29 : 12:04:05
|
| Aha! I have found what I was looking for!This is taken from Microsoft's Developer Network:http://msdn2.microsoft.com/en-us/library/ms167409.aspxDefining a Fact RelationshipUsers sometimes want to be able to dimension measures by data items that are in the fact table or to query the fact table for specific additional related information, such as invoice numbers or purchase order numbers related to specific sales facts. When you define a dimension based on such a fact table item, the dimension is called a fact dimension. Fact dimensions are also known as degenerate dimension. Fact dimensions are useful for grouping together related fact table rows, such as all the rows that are related to a particular invoice number. Although you can put this information in a separate dimension table in the relational database, creating a separate dimension table for the information provides no benefit because the dimension table would grow at the same rate as the fact table, and would just create duplicate data and unnecessary complexity. |
 |
|
|
|
|
|
|
|
Measure displayed without Aggregation ?