Concrete problem modeling new requirement (increasing one dimension). Help needed! :)

Hello,

We have  a DW and recently we had a change to model new business aspects on it. Once we conclude the modeling of this new aspects a new requirement appeared and I am having troubles to fit it in the current model.

Our DW, that reflects the communciations of a smart grid, ONLY contains installed products in the field. So, logically, all the measures of the fact tables are related with installed devices.

However, with this new requirement, they pretend to analyze a few attributes of the elements in stock (so not installed in the field) -like manufacturer and hardware of the device- for example.

Obviusly, we have a dimension for the products on the field related with several facts that store the measures of the installed devices-

But what should I do with this new requirement? From my understanding, it would implicate to model a new aspect of the business. The stock, and construct associated fact tables to this... But this is not possible, because the development will be too big, so they have asked me if it wouldnt be possible to include just those attributes of the devices in stock without providing analytical measures.

To make it easier, the installed devices, from now D1, is SCD type 2

So I thoought, ok, we will resolve it with some dimensional analysis, here the possibilities I saw:

• I add an attribute to my dimension of devices D1, I call the attribute In_stock (Y/N) and I will be able to answer the question. However, this will cause, that the records with Y will have no link with fact tables. Is it a common practice?
  

• I also thought on putting an isolated dimension SCD type2 (devices_stock, not related with any fact table) and when in the future they want to analyze the stock we can resuse it, meanwhile we do dimensional analysis to answer their questions (mainly getting lists of the devices for certain periods )
  

• I have  also been suggested to create a dimension called STOCK D2, containing all devices (in stock and in the field), linking it  1..1 with the dimension installed devices D1. D1  is connected to the fact table, so the final picture would be d2- > d1 ->F_table. In this suggested method, the devices in stock but not on the field of D2, are not connected to the dim installed D1,  and they dont arrive to the fact table, so d2 contains d1. But I dont like it, since its creating a meaningless snowflakle, and I dont like the fact that d2 contains d1 and d1 arrives to the fact. It is an inverted piramid, while I understand the hierarchies as piramids arriving the base to the fact table, not the peak.
  

Other ideas? What option do you like more from the 3 exposed?

Thanks a lot for spending your time in this questions, any help or point of view it really appreciated

I assume you are talking about meters and other capital equipment that is identified by serial numbers or some specific identification.  

Why complicate things?  Just put them in your existing dimension.    Why were they not there in the first place?  Is it a different source or is there a significant difference in attributes?

As far as analytics go, it would require new fact tables.

Hey,

Thanks for the advise.

Indeed, the stock comes form a new source, and such a source has prompted now with this new requirements (retrieve list from the stock).
We really have to take a decission this week, and for the moment I think the chosen decission would be to add the stock to the existing dimension as you suggest.

I only see two possible drawbacks:

-We have to explain it to the IT users, that the concept of the dimension has changed (for the business users we can handle this with a filter in the BI tool)
-The size of the dimension will be x2 (however, l the fact tables, accumulative snapshots mostly, we will keep the number of records per photo)

I appreciate your reply