Parameters Plugin additional solution

[intapiuser]

Step 1: The Fact Table

Here is my fact table, it contains an item and a price as well as a fake key. This will be necessary for the next steps.

Step 2: The Parameter Table

The key element is to rethink of a parameter as a dimension. 

So the first step is that we need to create a table that has all possible values a user could enter.

Now spoiler alert, we will only ever join a single row from this table in our queries so even if the table of possible values is extremely large, it is doubtful that the computation and storage costs of this approach would be significant, but obviously do your own testing. 

Step 3: The Join

Now that we have a list of all of our values, we need to join it to the fact table which has the values we want to multiply. But we need these values to be usable with any row in the fact table so we'll need to join the tables with a fake key. 

Add the table to our cube, connect it to our fact table on our fake key and we are good to go.

Step 4: The Filters

In our dashboard we need to create a single select filter for the parameters table. 

Step 5: The Value

In our widgets we can now use the value. You'll need to perform an aggregation on it, but since there is a single select filter, you'll only ever get one value. So AVG, SUM, MAX, MIN should all work equally well. 

And you're done!

Multiple Parameters

You can do this with multiple parameters just connect each one to your fact table. They can all use the same fake key column on the fact table. 

In this case, in your widgets, you will need to add an include all widget filter for the parameter foreign key column in the fact table. This ensures that Sisense joins the fact table to each parameter table independently instead of trying to join parameter tables to each other. 

Multiple Fact Tables:

This solution has only been vetted for single-fact data models. Multi-fact data models with parameters may be possible but will require much more careful preparation than above. For a multi fact, I would suggest reaching out directly to a Sisense expert. 

Conclusion:

Please see the example here