Aufbau Datenmodell für Stücklistenkalkulation

ukulele schrieb:
Code: 
WITH t(ebene,pfad,Objekt_ID,objektTyp,menge) AS (
    SELECT 1,
            cast(Objekt_ID AS VARCHAR(8000)),
            Objekt_ID,
            objektTyp,
            NULL
    FROM tblObjekt
    WHERE Objekt_ID = 234565
    UNION ALL
    SELECT t.ebene + 1,
            t.pfad + '.' + cast(c.Objekt_ID AS VARCHAR(8000)),
            c.Objekt_ID,
            c.objektTyp,
            z.menge
    FROM t
    INNER JOIN tblZuordnung z ON t.Objekt_ID = z.Mother_ID
    INNER JOIN tblObjekt c ON z.Child_ID = c.Objekt_ID
    WHERE t.ebene + 1 <= 100
)

-- Hier wird die Gesamtsumme über preis_berechnet berechnet
SELECT *,
        t.menge * tblDetaildaten.preis AS position_preis
        --SUM(t.menge * tblDetaildaten.preis) as gesamter_preis_berechnet
FROM t
LEFT JOIN tblDetaildaten ON t.Objekt_ID = tblDetaildaten.Objekt_ID;

Deine DB weiß sehr wohl das es 2 Türen im Golf gibt, ich habe deine Testdaten 1:1 übernommen. Die Menge fließt auch korekt in die Berechnung ein. Deine DB weiß allerdings nicht, was eine Tür von einem Golf und die Innenverkleidung kosten.
Zum Vergrößern anklicken....
@ukulele

Danke für deine Nachricht, aber wenn du dir den VW Golf im Beispiel anschaust gehören zu Ihm 2 Türen (oberste Baugruppe) diese setzt sich aber durch die zwei Türen aus nur noch zwei Innenverkleidungen. 2 Türgriffen.1 Lautsprecher, 2 Dämmungen und 5 Schrauben zusammen. Nicht wie der Skoda Oktavia aus 4 Türen und deren Mengen.

ebene pfad Objekt_ID objektTyp menge Objekt_ID bezeichnung preis position_preis 1 234565 234565 1 NULL 234565 VW Golf NULL NULL 2 234565.489282 489282 2 2 489282 Tür NULL NULL 3 234565.489282.673673 673673 2 [B] 4[/B] 673673 Inneverkleidung NULL NULL 3 234565.489282.939039 939039 3 [B]4[/B] 939039 Türgriff 2500 10000 3 234565.489282.672674 672674 3 [B]2 [/B] 672674 Lautsprecher 2500 5000 4 234565.489282.673673.363737 363737 3 [B]4 [/B] 363737 Dämmung 2250 9000 4 234565.489282.673673.373873 373873 3 [B]10 [/B] 373873 Schraube 100 1000

Die markierte Menge ist ja für den Skoda Oktavia.... und nicht den Golf. Wie würdest du die Umrechnung machen bzw. dort fehlt einfach der Zusammenhang zwischen Auto, Menge der Einzelteile....
 
Werbung:
Ron787878 schrieb:
@ukulele

Danke für deine Nachricht, aber wenn du dir den VW Golf im Beispiel anschaust gehören zu Ihm 2 Türen (oberste Baugruppe) diese setzt sich aber durch die zwei Türen aus nur noch zwei Innenverkleidungen. 2 Türgriffen.1 Lautsprecher, 2 Dämmungen und 5 Schrauben zusammen. Nicht wie der Skoda Oktavia aus 4 Türen und deren Mengen.

ebene pfad Objekt_ID objektTyp menge Objekt_ID bezeichnung preis position_preis 1 234565 234565 1 NULL 234565 VW Golf NULL NULL 2 234565.489282 489282 2 2 489282 Tür NULL NULL 3 234565.489282.673673 673673 2 [B] 4[/B] 673673 Inneverkleidung NULL NULL 3 234565.489282.939039 939039 3 [B]4[/B] 939039 Türgriff 2500 10000 3 234565.489282.672674 672674 3 [B]2 [/B] 672674 Lautsprecher 2500 5000 4 234565.489282.673673.363737 363737 3 [B]4 [/B] 363737 Dämmung 2250 9000 4 234565.489282.673673.373873 373873 3 [B]10 [/B] 373873 Schraube 100 1000

Die markierte Menge ist ja für den Skoda Oktavia.... und nicht den Golf. Wie würdest du die Umrechnung machen bzw. dort fehlt einfach der Zusammenhang zwischen Auto, Menge der Einzelteile....
Zum Vergrößern anklicken....

Die Preise der Einzelteile bleiben gleich nur die Mengen Zuweisung. :)
 
Servus ukulele,

@ukulele

ich habe mir nochmal Gedanken gemacht und die Inserts angepasst, jedoch beim rekursiven Aufruf fehlt mir noch die Erfahrung, wie ich auf den richtigen Gesamtwert komme.

Hier meine Inserts:

SQL: 
CREATE TABLE tblObjekt(
    Objekt_ID INT NOT NULL,
    objektTyp SMALLINT NOT NULL --1=Produkt; 2=Baugruppe; 3=Einzelteil
    );

INSERT INTO tblObjekt VALUES(123456,1);
INSERT INTO tblObjekt VALUES(489282,2);
INSERT INTO tblObjekt VALUES(673673,2);
INSERT INTO tblObjekt VALUES(676376,2);
INSERT INTO tblObjekt VALUES(334242,2);
INSERT INTO tblObjekt VALUES(939039,3);
INSERT INTO tblObjekt VALUES(363737,3);
INSERT INTO tblObjekt VALUES(373873,3);
INSERT INTO tblObjekt VALUES(672674,3);
INSERT INTO tblObjekt VALUES(478728,3);
INSERT INTO tblObjekt VALUES(456534,3);
INSERT INTO tblObjekt VALUES(522231,3);

INSERT INTO tblObjekt VALUES(234565,1);

CREATE TABLE tblZuordnung(
    Mother_ID INT NOT NULL,
    Child_ID INT NOT NULL,
    menge INT NOT NULL
    );

INSERT INTO tblZuordnung VALUES(123456,489282,4);
INSERT INTO tblZuordnung VALUES(123456,676376,1);
INSERT INTO tblZuordnung VALUES(489282,939039,1);
INSERT INTO tblZuordnung VALUES(489282,673673,1);
INSERT INTO tblZuordnung VALUES(489282,672674,2);
INSERT INTO tblZuordnung VALUES(673673,363737,1);
INSERT INTO tblZuordnung VALUES(673673,373873,2);
INSERT INTO tblZuordnung VALUES(676376,478728,1);
INSERT INTO tblZuordnung VALUES(676376,334242,1);
INSERT INTO tblZuordnung VALUES(676376,522231,10);
INSERT INTO tblZuordnung VALUES(334242,373873,8);
INSERT INTO tblZuordnung VALUES(334242,456534,1);

INSERT INTO tblZuordnung VALUES(234565,489282,2);

CREATE TABLE tblDetaildaten(
    Objekt_ID INT NOT NULL,
    bezeichnung varchar(50) NOT NULL,
    preis INT
    );

INSERT INTO tblDetaildaten VALUES(123456, 'Skoda Oktavia', NULL);
INSERT INTO tblDetaildaten VALUES(489282, 'Tür', NULL);
INSERT INTO tblDetaildaten VALUES(939039, 'Türgriff', 2500);
INSERT INTO tblDetaildaten VALUES(673673, 'Inneverkleidung', NULL);
INSERT INTO tblDetaildaten VALUES(363737, 'Dämmung', 2250);
INSERT INTO tblDetaildaten VALUES(373873, 'Schraube', 125);
INSERT INTO tblDetaildaten VALUES(672674, 'Lautsprecher', 625);
INSERT INTO tblDetaildaten VALUES(676376, 'Motorhaube', NULL);
INSERT INTO tblDetaildaten VALUES(478728, 'Blech', 8000);
INSERT INTO tblDetaildaten VALUES(334242, 'Scharnier', NULL);
INSERT INTO tblDetaildaten VALUES(456534, 'Halterung', 5000);
INSERT INTO tblDetaildaten VALUES(522231, 'Niete', 100);

INSERT INTO tblDetaildaten VALUES(234565, 'VW Golf', NULL);

Rekursive Aufruf:

SQL: 
WITH t(ebene,pfad,Objekt_ID,objektTyp,menge) AS (
    SELECT 1,
            cast(Objekt_ID AS VARCHAR(8000)),
            Objekt_ID,
            objektTyp,
            NULL
    FROM tblObjekt
    WHERE Objekt_ID = 123456
    UNION ALL
    SELECT t.ebene + 1,
            t.pfad + '.' + cast(c.Objekt_ID AS VARCHAR(8000)),
            c.Objekt_ID,
            c.objektTyp,
            z.menge
    FROM t
    INNER JOIN tblZuordnung z
    ON t.Objekt_ID = z.Mother_ID
    INNER JOIN tblObjekt c

    ON z.Child_ID = c.Objekt_ID
    WHERE t.ebene + 1 <= 100
    )

SELECT t.*, tblDetaildaten.bezeichnung, tblDetaildaten.preis, (t.menge * tblDetaildaten.preis) as preis_berechnet
FROM t
LEFT JOIN tblDetaildaten ON t.Objekt_ID = tblDetaildaten.Objekt_ID
ORDER BY t.pfad

Im angehängten Bild der Output und die gewünschte Multiplikation. Wie würdest du es anstellen?
 

Anhänge

  • Screenshot1.PNG
    Screenshot1.PNG
    44,9 KB · Aufrufe: 4
Ach jetzt verstehe ich das erst. Also die Menge in der Zuordnungstabelle bezieht sich nie auf das ganze Fahrzeug sondern immer nur auf die Beziehung Objekt zu Unterobjekt, Baugruppe zu Inhalt, wie auch immer. Das heißt effektiv musst du während der Rekrusion die benötigte Menge errechnen. Das Ergebnis ist dann die Menge aller Teile für das komplette Fahrzeug.
Code: 
WITH t(ebene,pfad,Objekt_ID,objektTyp,menge) AS (
    SELECT 1,
            cast(Objekt_ID AS VARCHAR(8000)),
            Objekt_ID,
            objektTyp,
            1
    FROM tblObjekt
    WHERE Objekt_ID = 123456
    UNION ALL
    SELECT t.ebene + 1,
            t.pfad + '.' + cast(c.Objekt_ID AS VARCHAR(8000)),
            c.Objekt_ID,
            c.objektTyp,
            t.menge * z.menge
    FROM t
    INNER JOIN tblZuordnung z
    ON t.Objekt_ID = z.Mother_ID
    INNER JOIN tblObjekt c

    ON z.Child_ID = c.Objekt_ID
    WHERE t.ebene + 1 <= 100
    )

SELECT t.*, tblDetaildaten.bezeichnung, tblDetaildaten.preis, (t.menge * tblDetaildaten.preis) as preis_berechnet
FROM t
LEFT JOIN tblDetaildaten ON t.Objekt_ID = tblDetaildaten.Objekt_ID
ORDER BY t.pfad
 
Mega du bist Spitze 😀.Wenn ich dann vom Skoda Oktavia, nur die Baugruppen Türen wissen will muss ich aber grundlegend was am Select ändern oder? Suche ich nur auf die Materialnummer der Baugruppe ,bekomme ich ja nur eine Tür und nicht die vier :).
 
Nun, da kann man sich verschiedene Ansätze vorstellen. Zunächst einmal kann die WHERE-Bedingung im ersten Select auf Objekt_ID ja auch mal entfallen, dann bekommt man das gesammte Sortiment. Wenn man jetzt hier nicht nur den Wagen, sondern auch die Baugruppe eingrenzen will (also quasi um eine Rekursion auf alles zu verhindern), ginge das im zweiten Select. Allerdings könnte noch eine Baugruppe zwischen Fahrzeug und Türen sein, das weiß ich ja in dem Moment noch nicht.

Also besser fände ich es erstmal, im Äußeren Select auf
WHERE tblDetaildaten.bezeichnung = 'Tür'
einzugrenzen, dann bekommst du auch deine 4 Türen allerdings nur diese und nicht ihre weiteren Bestandteile. Mit
WHERE t.pfad LIKE '%489282%'
könnte man auch den Teilebaum prüfen, das wären dann auch alle Elemente unter Tür. Mann kann sich ja auch einen Pfad mit den sprechenden Namen der Teile erzeugen.

Oder man geht die Sache ganz anders an. Wenn man z.B. ein bestimmtes Teil hat / sucht, will man vielleicht sowohl wissen, was drinne steckt, als auch in welchen Kfz das Teil verbaut wird (oder gleich alle Teile dieser Kategorie). Dann würde man anders arbeiten und in beide Richtungen rekursiv arbeiten. Das kann ich aber grade zwischendurch nicht machen, vielleicht heute Abend.
 
Wie du schon angesprochen hast, möchte ich z.B. für den Skoda Oktavia die Kosten für die benötigten Türen oder auch der Innenverkleidung. Dementsprechend benötige ich alles was darunter hängt. Eine einzelne Tür kann ich ja über die where-Bedingung holen.

Beste Grüße
 
Dann würde ich tatsächlich über
WHERE t.pfad LIKE '%489282%'
gehen, auch wenn LIKE nicht immer ideal ist. Allerdings reden wir hier vermutlich nicht von gigantisch vielen Datensätzen und es ist schlicht am einfachsten. Du kannst noch das oberste Objekt als eigene Spalte mit führen, ist vielleicht gut für die Übersicht.
Code: 
WITH t(root_ID,ebene,pfad,Objekt_ID,objektTyp,menge) AS (
    SELECT    Objekt_ID,
            1,
            cast(Objekt_ID AS VARCHAR(8000)),
            Objekt_ID,
            objektTyp,
            1
    FROM tblObjekt
    WHERE    objektTyp = 1
--    WHERE Objekt_ID = 123456
    UNION ALL
    SELECT    t.root_ID,
            t.ebene + 1,
            t.pfad + '.' + cast(c.Objekt_ID AS VARCHAR(8000)),
            c.Objekt_ID,
            c.objektTyp,
            t.menge * z.menge
    FROM t
    INNER JOIN tblZuordnung z
    ON t.Objekt_ID = z.Mother_ID
    INNER JOIN tblObjekt c
    ON z.Child_ID = c.Objekt_ID
    WHERE t.ebene + 1 <= 100
    )

SELECT rDetail.bezeichnung,t.*, tblDetaildaten.bezeichnung, tblDetaildaten.preis, (t.menge * tblDetaildaten.preis) as preis_berechnet
FROM t
INNER JOIN tblDetaildaten rDetail ON t.root_ID = rDetail.Objekt_ID
LEFT JOIN tblDetaildaten ON t.Objekt_ID = tblDetaildaten.Objekt_ID
--WHERE tblDetaildaten.bezeichnung = 'Tür'
WHERE t.pfad LIKE '%489282%'
ORDER BY t.pfad
 
@ukulele

Servus,

erstmal frohes Neues :).

ich melde mich auch nochmal, habe alles soweit umgesetzt und noch eine vor aggregierte Tabelle eingesetzt, um die Ladezeiten zu verkürzen. Jedoch wenn man sich einzelne Baugruppen anschauen möchte braucht man ja die gesamte Stückliste. Hierfür habe ich Views angelegt, wie funktioniert es in PowerBI bzw. wie machst du sowas, wenn über einen Filter die jeweilige View angezeigt werden soll z.B. Skoda oder Golf? Gibt es in PowerBI eine andere Möglichkeit als in Tableau oder bin auf dem falschen Weg :)

VG
 
@ukulele

Da Rekursion zwar in Tableau nicht direkt über einen generischen bzw. flexiblen Filter möglich ist, wäre nur noch eine Option die mir eingefallen ist, die Rekursion auf unions auf 10 ebenen umzubauen. Vielleicht hast du da mehr Erfahrungswerte und eine bessere Idee :)
 
Werbung:
Also mit PowerBI habe ich noch nicht viel gemacht, vor allem keine Stücklisten. Die simpelste Variante wäre die gesamte Rekursion in SQL zu machen, per View alle Datensätze bereit zu stellen und in PowerBI die Ergebnismenge zu filtern. Das ist natürlich Grütze wenn es um sehr viele Datensätze geht. Was PowerBI da kann oder nicht kann weiß ich leider nicht.
 
Zurück
Oben