SSAS Hierarchie mit n-m Verknüpfung

Question

Hallo,
ich habe ein SSAS Projekt (SQL Server 2008R2)
Ich habe Mitarbeiter, eine Gruppierung dieser Mitarbeiter und eine n-m Tabelle für die Zuordnung der Mitarbeiter zu der Gruppierung.
Zusätzlich habe ich eine Faktentabelle mit Arbeitszeiten.

Tabelle Mitarbeiter
IDMitarbeiter, MitarbeiterName

Tabelle Gruppierung
IDGruppierung, Gruppenname, IdParent

Tabelle Zuordnung
IdGruppierung, IdMitarbeiter

Tabelle Stundensatz
IDStundensatz, IdMitarbeiter, Arbeitszeit

Nun will ich auswerten können, welche Gruppen von Mitarbeitern wieviel Arbeitszeit für etwas verbraucht haben.
Ein Mitarbeiter kann in verschiedenen Gruppen vorhanden sein.
Je Gruppe sollen alle Mitarbeiter berechnet werden. Wenn jedoch ein Mitarbeiter in verschiedenen Gruppen vorhanden ist, soll in der entsprechenden Obergruppe dieser Mitarbeiter nur einmal berechnet werden.
Ich will eine Hierarchie, in der ich die Gruppen bis zum Mitarbeiter erweitern kann. Je Gruppe und je Mitarbeiter soll die entsprechende Arbeitszeit angezeigt werden.

Ich habe es schon hinbekommen, dass ich die Gruppen anzeigen kann.
Ich habe es auch schon hinbekommen, dass ich die Mitarbeiter anzeigen kann.
Jedoch habe ich leider noch keine Lösung, dass ich die Gruppen UND die Mitarbeiter in einer Hierarchie (einer Dimension) angezeigt bekomme.

Hat jemand einen Ansatz dafür?

Thomas

Anbei das SQL Server Script

USE M2MDB;
 
GO
 
 
CREATE TABLE Mitarbeiter
 
(
 
  IDMitarbeiter int PRIMARY KEY,
 
  MitarbeiterName varchar(50),
 
);
 
 GO
 
 
 CREATE TABLE Gruppierung
 
(
 
  IDGruppierung int PRIMARY KEY,
 
  Gruppenname varchar(50),
 
  IdParent int,
 
);
 
GO
 
 
CREATE TABLE Zuordnung
 
(
 
  IdGruppierung int,
 
  IdMitarbeiter int,
 
  CONSTRAINT Zuordnung_pk PRIMARY KEY (IdGruppierung, IdMitarbeiter),
 
  CONSTRAINT FK_Mitarbeiter
 
      FOREIGN KEY (IDMitarbeiter) REFERENCES Mitarbeiter (IDMitarbeiter),
 
  CONSTRAINT FK_Zuordnung
 
      FOREIGN KEY (IdGruppierung) REFERENCES Gruppierung (IdGruppierung),
 
);
 
GO
 
 
CREATE TABLE Stundensatz
 
(
 
  IDStundensatz int PRIMARY KEY,
 
  IdMitarbeiter int,
 
  Arbeitszeit int,
 
  CONSTRAINT FK_Stundensatz
 
      FOREIGN KEY (IDMitarbeiter) REFERENCES Mitarbeiter (IDMitarbeiter),
 
);
 
GO
 
INSERT INTO dbo.Mitarbeiter
 
VALUES ('1', 'Meier'), ('2', 'Müller'), ('3', 'Huber');
 
GO
 
INSERT INTO dbo.Gruppierung
 
VALUES ('1', 'Obergruppe','0'), ('2', 'Untergruppe1','1'), ('3', 'Untergruppe2','1');
 
GO
 
INSERT INTO dbo.Zuordnung
 
VALUES ('2', '1'), ('2', '2'), ('3', '1'), ('3', '3');
 
GO
 
INSERT INTO dbo.Stundensatz
 
VALUES ('1', '1','10'), ('2', '2','20'), ('3', '3','30') ,('4', '1','100'), ('5', '2','200'), ('6', '3','300');
 
GO

Answer 1

Hallo Thomas,

Schau Dir mal folgende Artikel an http://technet.microsoft.com/en-us/library/ms170463.aspx vielleicht kann es dir weiterhelfen.

Gruss,

Ionut

---

Ionut Duma, MICROSOFT   Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip„Entwickler helfen Entwickler“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.

Answer 2

Jedoch habe ich leider noch keine Lösung, dass ich die Gruppen UND die Mitarbeiter in einer Hierarchie (einer Dimension) angezeigt bekomme.

Hallo Thomas,

woran genau hapert es denn, an der Modellierung oder an der MDX Abfrage? Sieh Dir mal das AdventureWorks Cube Beispielprojekt (http://msftdbprodsamples.codeplex.com) an, dort warden alle Thematik wie auch n:m Beziehungen behandelt.

Dort gibt es auch Product mit einer einfachen Gruppierung, die kannst Du zusammen so abfragen:

SELECT
    {
      [Measures].[Internet Sales Amount]
    } ON 0
    ,
    { [Product].[Category].[Category]
    * [Product].[Product].[Product]
    } ON 1
FROM [Adventure Works]

Also einfach mit * (Cross Join) zusammen setzen.

Bist Du Dir übrigens mit Deinem Datenmodell sicher? Momentan gibt es keine Zuordnung, für welche Gruppe ein Mitarbeiter die Zeit aufgebracht hat. Ist ein Mitarbeiter z.B. in 3 Gruppen und Du wertest über die Gruppen aus, werden seine Gesamtzeiten auch 3mal ausgegeben und fließen 3mal in die Gesamtsumme ein.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 3

Hi Ionut, Hi Olaf,

danke für den Link.

Leider löst das jedoch nicht mein Problem. Eine m-n Beziehung habe ich schon hinbekommen. Und auch eine  Hierarchie ist nicht das Problem, sondern beide Zusammenzuhängen:

Beispiel:

Obergruppe

   -Untergruppe 1

      -Meier

      -Müller

   -Untergruppe 2

      -Meier

      -Huber

Die Gruppen "Untergruppe 1" und "Untergruppe 2" kann ich in der Hierarchie anzeigen. Ich habe auch eine zweite Dimension mit "Meier" "Müller" "Huber". Ich will jedoch alle Einträge in einer Dimension zur Verfügung haben. Momentan muss ich beide Dimensionen in den Cube Browser ziehen, wenn ich bis zur letzten Ebene auswerten will.

Vielleicht habe ich einen falschen Ansatz in der Datenstruktur?

Zu der Frage:

... Momentan gibt es keine Zuordnung, für welche Gruppe ein Mitarbeiter die Zeit aufgebracht hat...

Ja, das ist korrekt so. Die Gruppe hat keine direkte Zuordnung zu den Stundensätzen.

Jedoch wird durch die n-m Beziehung in der Gesamtzeit der entsprechende Mitarbeiter nur 1mal ausgegeben (nicht wie von dir geschrieben 3mal!) Das ist so gewollt und funktioniert auch schon.

Thomas

Answer 4

Ich habe auch eine zweite Dimension mit "Meier" "Müller" "Huber". Ich will jedoch alle Einträge in einer Dimension zur Verfügung haben. Momentan muss ich beide Dimensionen in den Cube Browser ziehen, wenn ich bis zur letzten Ebene auswerten will.

Hallo Thomas,

das habe ich jetzt nicht verstanden; "Meier" "Müller" sind doch wohl Member eine Attributes wie "Mitarbeiter"; was musst Du da 2mal verwenden?

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 5

Hi Olaf,

Ich habe eine Rekursive Parent-Child Dimension "Gruppierung"

Ich habe eine Dimension "Mitarbeiter" (1:n zur Faktentabelle)

Ich habe eine n-m Beziehung dieser beiden Dimensionen mit einer Zwischenmeasergruppe "Zuordnung" zu meiner Faktentabelle "Stundensatz"

Wenn ich nun die Mitarbeiter der entsprechenden Gruppierung sehen will, muss ich momentan beide Dimensionen verwenden. Das will ich jedoch vermeiden. Ich hätte gerne, dass in einer Dimension zuerst die Gruppierung und dann (automatisch) die Mitarbeiter davon angezeigt werden. (Und das ganze muss dann per n-m zur Faktentabelle miteinander verküpft werden können)

Ich hoffe, das ist nun verständlicher?

Thomas

Answer 6

Hallo Thomas,

den "Browser" in BIDS/SSMS würde ich nicht gerade als Maß aller Dinge betrachten, das ist nur ein sehr einfaches Tool um schnell die Daten anzuzeigen.

Grundsätzlich musst Du beide Dimensionen verwenden, wenn Du auch beide angezeigt werden sollen. Woher soll das Frontend den wissen, was wie angezeigt und wie aggregiert werden soll; nur auf Gruppe oder nur auf Mitarbeiter oder auf der Kombination (und dann in welcher Reihenfolge)?.

Du kannst aber eine Hierachy in der Dimension erstellen und beim "einfügen" werden dann beide Dimensionsattribute hinzugefügt/dargestellt:

P.S.: Das "Gruppierung" eine rekursive Parent-Child Dimension ist, ist eine neue Info, das ging aus Deinem Datenmodel nicht hervor. Das Du das zumindest in der "Data Source View" entsprechend definiert?

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 7

Hallo Olaf,

sorry, dass ich diese Rekursion vergessen habe zu erwähnen.

In deinem Bild ist nun aber der Gruppenname keine Rekursion, oder? Geht das auch noch?

Ohne diese neue Dimension sieht es momentan so bei mir aus

mit der neuen Dimension sieht es so aus (wie gesagt, fehlt hier die Rekursion) Vermutlich habe ich in der Verknüpfung einen Fehler gemacht, da nun nicht mehr richtig berechnet wird! Wie muss ich das miteinander verknüpfen?

Thomas

Answer 8

Hallo Thomas,

wenn man immer für alle Attribute die Gesamtsumme angezeigt bekommt, liegt das immer daran, das die Beziehung zwischen der Dimension und der Measure Group nicht definiert ist. Wenn die Beziehung aufgrund des Datenmodelles eindeutig ist, wird die automatisch festgelegt, ansonsten muss man sie einmal manuell definieren; das kannst Du im Cube Entwurf unter "Dimension Usage" machen; z.B. so:

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 9

Hi Olaf,

das hat funktioniert! Super, vielen Dank.

Jetzt fehlt nur noch, dass diese Dimension auch noch zusätzlich die Parent-Child Hierarchie der Gruppierung anzeigt.

Thomas

Answer 10

Da muss ich passen; da weiß ich nicht mal ob/wie es evtl. geht. Im AdventureWorks Beispielprojekt gibt es 4 Dimensionen mit Parent-Child, aber bei keiner ist eine Hierachy definiert.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 11

Hi Olaf,

trotzdem danke für eine Hilfe. Deine Infos haben mich schon ein Stück weitergebracht.

Vielleicht hat ja jemand anders dazu noch eine Idee?

Thomas

Answer 12

Hallo Thomas,

eine Option hätte ich noch: Du kannst im Cube unter "Calculations" auch "Named Sets" anlegen, also vordefinierte Sets. Das kann so aussehen:

CALCULATE;  

CREATE DYNAMIC SET CURRENTCUBE.[Gruppierungs + Level 2]
 AS {
    NONEMPTY(  { [Gruppierung2].[Id Parent].[Level 02]}
             * { [Gruppierung2].[Gruppenname].[Gruppenname] }
             * { [Mitarbeiter].[ID Mitarbeiter].[ID Mitarbeiter] }
             , [Measures].[Arbeitszeit])
};

hier also ein CrossJoin über Gruppe, erstes Child und die Mitarbeiter Hierachy. Im BIDS Broswer wirst Du es nicht nutzen können, aber problemlos in MDX:

und vor allem in richtigen BI Clients wie MS Excel:

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 13

Hi Olaf,

danke für den Tipp, jedoch habe ich noch etwas nicht "erwähnt". Meine Parent-Child Hierarchie kann beliebig tief werden. Bedeute: Eine feste Zuordnung auf 2 Ebenen reicht leider nicht.

Und außerdem hätte ich gerne in "einem richtigen BI Clients wie MS Excel:", dass man die ganze Hierarchie "aufklappen" kann.

Aber ich glaube so langsam, dass ich mich mit den 2 unterschiedlichen Dimensionen "Gruppierung" und "Mitarbeiter" begnügen werden. Die "Gruppierung" kann man aufklappen, und sobald man die "Mitarbeiter" sehen will, muss dann diese dann eben "reinziehen"

Thomas

Answer 14

Hallo Thomas,

 Du kannst die Lösung mit Deinem Tabellendesign nur in einer SP und temporären Tabellen (TT)  lösen, indem Du die einzelnen MA in die TT einfügst und die entsprechenden Prozessschritte auführst.

(z. Glück kann der SQL-Server als einer der wenigen Engines in Unterstützung für den dieses.)

Ich arbeite sehr viel mit TTs. (insbesondere Session-TT)  Leider kennen nur die wenigsten Entwickler diese vorzügliche Möglichkeit.
Du hast aber ein anderes Problem. Dies liegt im Design Deiner Gruppentabelle.

Du mußt einen String-Index einfügen z.B. in der Form aaaa(0)bbbb(1)cccc(2)dddd(3) - abcd sind Zahlen.
Damit hast du 10000 Childs pro Parent (0)(1) usw. können weggelassen werden, wenn man pro ID eine feste Stellenanzahl festlegt.
(0)(1) usw. sind also die Parentebenen.

Mit Sort auf diesen Index kann man mit einer Abfrage sofort die richtige Reihenfolge erreichen, so daß man sofort z.B. ein treeview füllen kann.
Mit Stm. like '0002(1)%(1)' können alle Kinder des Parents 0002 und mit  like '0002(1)%(2)%' alle weiteren Childs einer anderen Untergruppe gelesen werden.

Mit diesem Design brauchst Du nur noch die MA mit den Gruppen zu joinen und Du weist bei jeder Gruppe, welche Parents sie haben. (ich mach' dies bis 30 Ebenen)

Timo

Answer 15

Hallo Thomas,

bist Du hier weitergekommen?

Gruss,

Ionut

---

Ionut Duma, MICROSOFT   Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip„Entwickler helfen Entwickler“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.

Answer 16

Hallo,

auch der Tip von Timo hat mich nicht richtig weitergebracht.

Jedoch verwende ich nun folgende Variante:

Ich habe 2 unterschiedlichen Dimensionen "Gruppierung" und "Mitarbeiter". Die "Gruppierung" kann man aufklappen, und sobald man die "Mitarbeiter" sehen will, muss dann diese dann eben "reinziehen"

Danke euch allen.

Thomas