sql multi value dimension

Question

(This is my first question ever asked so please bear with me here guys)

I created a data warehouse on SQL Server 2014 Enterprise and I'm struggling mapping a fact to multiple values in one dimension.

Imagine a data center with servers reserved for specific services and customers. Those servers have specific components like network adapters, cpus or operating systems.

A few rows from my fact table would look like this:

ServerID   CustomerID   OSID   ServiceID   NetworkGroupID  CPUID
1           1            1        1           1               1
2           1            2        1           2               1
3           2            1        2           2               2

Server 1 and 2 belong to Customer 1. Server 1 is running OS 1, the other OS 2. Both provide Service 1. Server 1 has a set of network adapters identified as '1', the other 2. These sets contain multiple network adapters (some virtual, some physical).

My Network Dimension has the attributes ID and NetworkAdapterName and I would need the ID to appear multiple times like so:

ID   NetworkAdapterName
1      IntelAdapter#1
1      IntelAdapter#2
1      VirtualAdapter#1
2      IntelAdapter#1
2      DellAdapter#1
3      VirtualAdapter#1
3      VirtualAdapter#2

I figured I would need some kind of sub dimension acting as a bridge like so:

NetworkGroupID   NetworkID
  1              1
  1              2
  1              3
  2              1
  2              5
  3              3
  3              4

NetworkID NetworkAdapterName
1         IntelAdapter#1
2         IntelAdapter#2
3         VirtualAdapter#1
4         VirtualAdapter#2
5         DellAdapter#1

But then how would I be able to define a primary key or a relation in general from the fact table to the bridge dimension, since there are multiple appearances of the same value as NetworkGroupID?

(The same occurs with the CPU and OS but once I get it working for the network adapters I would be able to do it for other components as well.)

Edit: In the end I want to build a cube in SSAS that can show me the multiple components one server has.

Answer 1

You could create a table (or dimension) defining NetworkGroup:

CREATE TABLE NetworkGroup (
    NetworkGroupID int          not null PRIMARY KEY,
    Name           nvarchar(50) null
);

Then use your "bridge" table (a.k.a. Junction Table) to join it with your Network table:

CREATE TABLE NetworkGroupNetwork (
    NetworkGroupID int not null,
    NetworkID      int not null,

    CONSTRAINT PK_NetworkGroupNetwork
        PRIMARY KEY (NetworkGroupID, NetworkID),

    CONSTRAINT FK_NetworkGroupNetwork_NetworkGroup
        FOREIGN KEY (NetworkGroupID)
        REFERENCES NetworkGroup (NetworkGroupID),

    CONSTRAINT FK_NetworkGroupNetwork_Network
        FOREIGN KEY (NetworkID)
        REFERENCES Network (NetworkID)
);