Creating a Composite View
For the Sales department at ALPHA, you can build a single, composite view of ALPHA’s business data from the three individual views you have created: ViewOrder, ViewSupplier, and ViewSales. There is no need to access the data sources any more.
To create the composite view
1. Expand Shared > sources in the resource tree.
2. Create a new view named CompositeView.
3. Drag the following views and drop them into the Model panel of CompositeView:
— ViewOrder
— ViewSales
— ViewSupplier
4. Join ProductID in ViewOrder with ProductID in ViewSales.
5. Join ProductID in ViewSales with ProductID in ViewSupplier.
6. In the Grid panel, limit the query to the following columns:
ViewOrder.*
ViewSales.ProductName
ViewSales.LeadTime
ViewSupplier.TransactionID
ViewSupplier.DateRequired
ViewSupplier.DatePromised
ViewSupplier.ShipDate
ViewSupplier.SupplierID
ViewSupplier.SupplierName
ViewSupplier.SupplierContactName
ViewSupplier.SupplierPhoneNumber
7. Save the view.
8. Execute the view. Result rows 1-50 are displayed.
9. In the result set displayed in the Result panel, look for a row where OrderID = 24 and Transaction ID = 30. There are multiple rows that fit this criteria in the result rows 1-150.
10. Click Load More Results in the Result panel to browse more rows.
The rows have the following data:
OrderID: 24
Status: open
OrderDate: 2003-02-06
CompanyName: Landmark Systems
CustomerContactFirstName: Joyce
CustomerContactLastName: Landers
CustomerContactPhone: (212) 333-1000
ProductID: 23
Discount: 0.05
ProductName: Widget 5
LeadTime: 1 Day
TransactionID: 30
DateRequired: 2003-02-10
DatePromised: 2003-02-10
ShipDate: 2003-02-12
SupplierID: 5
SupplierName: Good Supplies International
SupplierContactName: Justin Glory
SupplierPhoneNumber: (415) 665-8000
This result set provides a consolidated view of the customer-reported issue, and also contains information that can be used to find a solution to the current problem. You can contact the customer and offer a better discount. You can also contact the supplier to report the seriousness of the problem and negotiate a price reduction.
CompositeView is the single unified view that represents your business solution.
11. Close the CompositeView tab.
Optionally, you can publish each view to make it available to client applications that connect to the server through JDBC and/or ODBC. To do so, you must publish a view as a TDV database table. After you publish a view, you can reuse it as a relational database table to query further.
For further details on publishing, see the TDV User Guide.