Source Mapping
In this Example we use Data Store that we created here.
- Go to the “Source Mapping” tab.
- Click on the “Add Source” button.
- Find the Fact table “HistProductSales” in the Item Picker pop up and click “Ok”.
Notice that the data in the HistProductSales table is not in the same format as the data store we just created. One alternative is to use the SQL Transform field to write SQL code that transform your data. Another option is to create a view and use the view as your data source.
You can copy the SQL statement below into the Sql Transform window. We do this to create a column with margin % based on the data in the fact table.
SELECT
[DepartmentID],
[ProductID],
SUM([Amount]) AS HistSalesLY,
(1-(SUM([Amount])/SUM(COGS)))*-1 AS MarginPCT
FROM @Object[HistProductSales, fact].DbObjectName
WHERE [DatasetID] = 'Actual' and YEAR(Transdate) = '2009'
Group BY
[DepartmentID],
[ProductID]
- Paste the SQL query into the SQL Transform field
- Click “Save” or Ctrl + S
- Navigate to the “Column Bindings” area
- Map your Source columns to your Data Store columns or use the “Auto Bind” function. Your mapping should look like this:
- Click “Save” or Ctrl + S
- Click on the “Add Source” button. Select the “Department” Dimension in the Item Picker pop-up and click “Ok”
- Use the “column Bindings” function to map the Dimension column DepartmentID against Data Store column DepartmentID. Make sure the binding type is set to “Relationship”.
- Repeat step 9 and 10 for the “Product” Dimension
- Click “Save” or Ctrl + S
- Right click on your Data Store “Product Sales” in the Solution Explorer pane on the right-hand side and select “Reload Data”. Click “Yes” in the Reload Data pop-up. This uses the query you entered in the SQL Transform field to load the selected data from the fact table into the data store.
- When the “Reload data” dataflow is completed close the Data Flow execution dialog box.
- Right click on your Data Store “Product Sales” and select “Browse Data” to explore the content of your Data Store