Index designer lets you easily create a BWA model if the source is already a simple star schema (or very close to a star schema). But what if your sources are files ? Or if you want to combine data from multiple sources (like two different DWH) ? What if your source is not a star schema at all ('facts' in multiple tables), ... Well in that case, you need some ETL work first to massage your data into the right format, before you use index designer to create the structure in BWA.
You can use Data Services to collect and transform the data, and even use it to model the target tables. This is done though a concept called "template tables". Template tables are tables that will be created on the fly by the Data Services engine and are usually used to "stage" the data before using it in further ETL processing. In our case we will use the template tables to define the target schema for fact and dimension tables, and you will see it is not even required to actually execute the job to create the tables, you can keep them as "virtual" tables and only use them to feed the index designer in order to create the final model in BWA.
The overall workflow looks like this:
- Step 1 - Create Data Services job with template tables
Create a Data Services job that reads all your source data needed for the BWA cube and load this into template tables (one template fact table and one or more template dimension tables).
- Step 2 - Use index designer on template tables
From the template tables start the index designer and model the cube (create joins between the tables and select columns to use as dimensions and measures).
- Step 3 - Generate Data Services jobs
From index designer generate the Data Services job to create and load the BWA index.
- Step 4 - Modify load job and remove template tables
Next in Data Services Designer modify the generated job and by replacing the loading dataflows with the dataflow from step 1 above where you replace the template table with the actual target from the BWA datastore.
With the job that results from this you will load the data directly from the original sources into the BWA star schema. Data will not get staged intermediately. It is even not mandatory to execute the job created in step 1 so that these template tables are never physically created, but it is a nice way to test your dataflow, usually with some sample data and not the full data set.
- BWA and Explorer homepage
- Overview of BWA design and load
- Refining the BWA load
- Advanced BWA design
- Configure Explorer