Table Design - Storing Mulitple Parameters Required for Data Warehouse Report [closed]

Question

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:

1. SSIS package to run the report from the 3rd party system (using provided parameters)
2. Save the data to a Staging table
3. 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?

Answer 1

In previous jobs where I had to do a similar thing (one report connecting to many parameters), I went for your first option:

• store them in a [child] SQL table which has just 2 columns (I used ParameterName and ParameterValue and each parameter is added as a row.

It's nice and normalised, plus things don't break when you have to add/remove/change parameters, since you don't have to change your table structure to add a new parameter.

Try to avoid RBAR (Row-By-Agonising-Row) processing in your function that parses the (variable size) list of parameters into 1 string. Number tables or recursive CTEs come in useful to keep the function runtimes down.