New attribute for dimension

Hi guys,

The reporting people said to me today "Could you please add the FIRST_SALE_DATE, LAST_SALE_DATE, NUMBER_OF_SELLS attributes into the product dimension?". Actually the requirements can be extracted by using ORDER fact table but If I add these columns to the ORDER fact then they can prepare report easily. What would you do ? Should I add those into the dimension or should not I accept that feature?

Thanks.

Isn't this information you would get by querying your Sales Fact table. They definitely don't belong in you Product Dim as they are constantly changing (apart from the FIRST_SALES_DATE, I guess)

Actually yes they can get the answer from fact table but it is easy to get from product dimension those kind of information instead of querying fact table and summarize the data. They want me to do that to actually develop reports easily. How should I convince them to not to do that?

Just hide the 'complexity' in your BI Tool's metadata, using DB Views or whatever other mechanism is appropriate to your circumstances. As a quick and dirty example, using a DB View:

Create v_Prod_Sales as "select prod_code, min(sales_date), max(sales_date), count(sales) from.... group by prod_code"

and then join to your Product Dim using prod_code or whatever the correct business key is

Hi,

other approach would be to create a summary materialized view and let the report pick up from the Mview, this way it wont impact the performance.

thanks

Derived attributes like these are common, particularly in retail marketing analysis. Usually performance is an issue if you try to derive the values on-the-fly, so storing them somewhere is typical. Storage does not need to be in the same dimension table. As the others suggested, a simple summarization of the sales data, grouped on the product dimension key, would suffice. It would serve as an extension of the product dimension.

Creating it could be a simple process you run nightly to regenerate the summary.

Thanks to everyone for answer. But I wonder that you mentioned about the views. How should I place the view in the Dimensional Model? OK, I got the idea, you said that you can prepare a view ( or table ) to prevent complexity for report developers but as far as I know, report developers should not use the table/view which are not exist in the model? How should I do that ?

- And also what are the disadvantages of adding new this types of columns to the dimension table?

A view or a materialized summary table are basically the same thing (other than performance differences). It would be another dimension table in the model with the same PK as the product table.

You could include the attributes in the primary dimension table, but it is more work to implement (you have to generate the numbers then update the dimension table). Alternately, you could implement a view that combines the product dimension and the additional table. Most DBMS's will not perform the join if attributes from the extra table are not referenced, so there would not be a performance impact on existing queries. You would rename the existing product dimension table and give the view the same name as the current product dimension so that existing queries do not need to be changed.

You will need to do your own testing to see which approach works best. If you are in retail, a view doing a summary of the fact would be the worst choice, since retail fact tables tend to be huge. How DBMS's support materialized views vary. It may or may not be workable depending on your system. If the DBMS keeps the MV in sync with the main table, the main concern would be how this impacts update performance on the main table. If the MV needs to be refreshed after updates to the main table, then it becomes a matter of wither it is any faster than simply rebuilding the summary.

If the data is transactional, that is to say it is insert only into the main fact, a faster process would be to just summarize the new data and apply it against the dimension (either a single table or an adjunct table).