Overview
A composite view allows you to connect to multiple different data sources and join data across these in a single view which users can then report off. Since the composite view is extracting data from multiple data sources there can be a long delay in report generation. To counter this Yellowfin stores the results sets of a composite view in the Yellowfin repository (or an alternative repository of your choice) and schedules the result set to be refreshed on a regular basis. In this way users writing reports will have a rapid response to their queries rather than having to wait for complex joins to be completed, and for IT these process intensive processes can be scheduled to run during off-peak times.
The view builder for a composite view differs from the standard drag and drop builder in that rather than displaying tables from a selected database it allows you to join existing Yellowfin views together and create virtual tables from any defined database.
Creating a Composite View
The process of creating a composite view is similar to creating a standard Yellowfin drag and drop view. The difference however is that instead of selecting tables from a specific source you will select pre-existing Yellowfin views from each source you have defined.
You will also have to define where the data from your source database will be stored.
Define Storage Database
The data from a composite view will be extracted out of your target databases and stored in a separate database. Therefore, you will have to define a target database to store your data into. The first step in this process is to specify which databases are available to store results from your composite view.
- Selecting a Composite View Data Store
Open your database connection record that you wish to save the results of your composite view into. You can use the Yellowfin connection or alternatively any SQL database of your choice.
The user specified in the source will have to have write access for the database selected.
Open your connection record and click 'YES' for Writable.
- Select Database from Initiate a Composite View
When initiating a new view (by clicking the create link) you will need to select 'composite view' from the "I want to author the view using" drop down.
Prepare Data for your Composite View
If you do not wish to write SQL through virtual tables that joins data from separate source systems then you will have to create an initial view for each data source that you wish to include in the composite view. This view will contain the subset of data and the keys which you will use to join in your composite view.
Build these views through the normal view builder process and save the views to your database. Ensure that the columns you wish to join on are included in the views you create. If you do not create initial views then you will have to use the 'Virtual Table' builder and write SQL when creating your composite view.
Note: All metadata applied to your baseline views will be applied in your composite view.
Joining Existing Views Together
Once you have initiated your composite view you will be able to join multiple Yellowfin views together and also create virtual tables for freehand SQL queries against each of your data sources. The process of creating a composite view is similar to the standard drag and drop view builder. You will select views, define joins and select the columns you wish to make available to your end users.
Choose views you wish to join. If you have already created a set of views for the source systems you wish to connect then you will see these in the left hand database tables list in the appropriate source system folder. You can treat these as normal tables and join them as you would a standard view.
In the example below a table has been selected from the Ski Team database as well as the Yellowfin database and a join created through the standard join process.
Creating Virtual Tables
If you do not have the data you wish already defined in a pre-existing view then you can create a virtual table. The virtual table popup will prompt you to specify which source system you are connecting to.
In the example below the virtual table SQL has an additional drop down for the source system. You will need to specify the source system for any freehand SQL statements that you wish to generate.
Note: that for any aggregations used such as sums etc you will have to follow the aggregate function with an 'as "Column Name"' statement so that Yellowfin can generate a valid column name.
Set the Refresh Schedule
Since a composite view is refreshed on a regular basis you will need to define the refresh schedule. This schedule is located in the view options section on the right hand side of your screen.
The view will be refreshed by default on a daily basis at 12 midnight. You can change the schedule based upon your specific needs.
Initial Data Population
In the view summary you can preview data created by your view by navigating to the Data Tab and populating the view. If you fail to do this no data will be returned in your report until the first cycle of the schedule has run.
- Go to the Data tab and click on the Populate Data Now link.
- You will now be told the view is being populated, click Refresh Page to view the data. If you have a large amount of data this may take some time to complete.
- Once the population is complete you will see a preview of the data your view will return.
