In a business where customers may execute multiple sales orders over time, the dimensional model contains two tables:
Suppose that, there is a business rule, whereby customers who have not executed any sales activity within the last three months, are considered to be "Inactive". Other customers, who have executed sales activity within the last three months, are considered to be "Active".
Is it good practice to include an "Active Status" column, which is derived from the fact data itself, as a dimensional attribute? If yes, could it also be handled as a Type II SCD?
Or else, is this a calculation which should be pushed to the analytics layer (e.g. modelled in power bi through DAX?) What considerations should be made?
In general, it is not good practice to include physical columns in your dimensional model that are dependent on when you run a query (age is the obvious example and your Active Status column is a similar example). Instead, you would create it as a calculated column in a view or in your BI tool.
However, if the performance of calculating this at run-time is too great then you may be forced to physically populate the column. In your case, you could set the active status to true when you ingest orders; however, to set the value to false you would need to run a (daily) process to check all your customers with an active status of true and check if their last sale was more than 3 months ago.