I am building a SQL Server Data Warehouse to store reporting data from a 3rd party system.
The reporting data is pulled from the 3rd party system by passing in a set of parameters (the parameters passed in could vary from minimum 3 to max 8. Depends on the report detail required by the user).
Here is my plans for the DW process:
- SSIS package to run the report from the 3rd party system (using provided parameters)
- Save the data to a Staging table
- Use stored proc to take data from staging to the Dimension and Fact tables
My question is: Does the above process sound correct, and what is the best way to store the parameters required to run the report?
- Should I store them in a SQL table which has just 2 columns (e.g. ReportSettingsV2 table) and the parameters are added as rows?
- Or should the table have each parameter as a column, and if a parameter is not required for a particular load of the report then that will just be marked as NULL (e.g. ReportSettings table)?
- Or a third option I am thinking would be to have the parameters stroed in an XML data type column?