Table Design - Storing Mulitple Parameters Required for Data Warehouse Report 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?
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.