:-[ - Novice Here... Needs more help... :-[
In the Foundation view (or Data Layer)
I have Order_header table (one row per order)
I have an Order_detail table (one row per order and item-ordered). The Order_detail table has things like shipping_date and weight and other interesting things.
I'd like to add some fields to my Order_header_Fact in my consolidation view (or Business Layer). Things like:
- first_ship_date -- which is minimum(Order_Detail.Shipping_date), for order_number
- last_ship_date -- which is maximum Order_Detail.Shipping_date), for order_number
- total_order_ship_weight -- sum (Order_detail.weight) Group by Order_number
I can create these functions BUT ...I do not get 1 row per Order. For each order I get the number of rows found on the Order_Detail.
To the user community, after some struggles, I came up with this solution ... but, is there a better solution out there?
On the database itself, I created a view with the summary information I need from the order_detail file, and saved the view. Then, I ran the metadata wizard and imported the view. After that I was able to create a new query subject in the metadata layer.
It seems cumbersome, but it works. Any better ideas?