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?

enter image description here

enter image description here

closed as primarily opinion-based by LowlyDBA, Erik Darling, hot2use, Mr.Brownstone, Marco Feb 20 '18 at 12:34

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.


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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.