Datenmodell-Zugriff auf Power Query Abfrage

[LB1972]

Servus zusammen,

ich bin auf der Suche nach einer Lösung für folgendes Szenario:

• Mit Hilfe von Power Query generiere ich eine Abfrage auf einen Excelnamen "Monate" (in einer externen Datei), deren Ergebnis ich im Datenmodell halte.
  
• Die Abfrage heißt bei mir "Monatsliste"; Die Spaltenüberschrift nenne ich "Monatsnamen".
  
• In einer Gültigkeitsabfrage möchte ich als zugelassene Liste die Werte aus [Monatsliste].[Monatsnamen] verwenden.
  
• [Monatsliste].[Monatsnamen] soll aber nicht als Tabelle in der Excel erscheinen, sondern ich möchte sie direkt aus dem Datenmodell abgreifen.
  

Geht das? Und wenn ja, wie?

Die Variationen "Monate", "Monatsliste" und "Monatsnamen" sollen nur die Übersichtlichkeit erhöhen, welche Auflistung ich jeweils in meiner Beschreibung meine.
Im angehängten Beispiel  habe ich die Vereinfachung gemacht, dass sich der Name "Monate", auf den sich die PQ-Abfrage bezieht, in der selben Datei befindet. Hier wollte ich diejenigen, die versuchen, mir zu helfen, nicht mit zwei Dateien "jonglieren" lassen. Ich hoffe, das verwirrt nicht mehr als es hilft.

Vielen Dank schon mal für eure Unterstützung
Gruß Ludwig

[maninweb]

Hallo,

was mich betrifft, wäre mir keine Möglichkeit bekannt, direkt aus dem Datenmodell eine Gültigkeitsliste zu erstellen.

Sofern Du ein Excel hast, was dynamische Arrays kennt, lassen sich aus dem Datenmodell die Monate dynamisch mit den Cubefunktionen ziehen und
anschließend ein Name vergeben, der auf das dynamische Array zugreift. Den Namen kann man wieder als Gültigkeitsliste verwenden. Allerdings,
so oder so, es braucht die Daten in der Tabelle. Von daher wäre es wohl einfacher die Liste in einer Tabelle aus PQ generieren zu lassen.

Zur Info, so sähe das dynamische Array dann als Formel mit Cubefunktionen aus ...

Code:

=CUBERANGELEMENT("ThisWorkbookDataModel";CUBEMENGE("ThisWorkbookDataModel";"[Monatsliste].[Monatsnamen].[All].Children");
 SEQUENZ(CUBEMENGENANZAHL(CUBEMENGE("ThisWorkbookDataModel";"[Monatsliste].[Monatsnamen].[All].Children"))))

Gruß

[LB1972]

Hallo maninweb,

Danke für deine Einschätzung.
Ich komm grad noch nicht dazu, deine Infos nachzuvollziehen und für mich auszuprobieren.
Gib mir bis zu Wochenenden Zeit, mich da reinzudenken.
Rückmeldung, wie's mir damit ergangen hat, folgt dann.
Nichtsdestotrotz sind mir natürlich weitere Meinungen dazu willkommen.

Merci
Gruß
Ludwig

[LB1972]

Hallo maninweb,

ich hab deinen Ansatz verstanden (immerhin... :70: ...).
und ich pflichte dir bei, dass ich mich damit am Ende wohl eher einmal im Kreis drehen würde, weil ich ja dann doch für meine Gültigkeit eine PQ-Tabelle bräuchte.
Hmmm, schade   .
Immerhin habe ich gelernt, dass man ein Datenmodell innerhalb der xl-Datei recht ordentlich über die Funktionsgruppe "CUBE..." ansprechen kann.
Das wusste ich bisher nicht.  

Die Erkenntnis, die CUBEMENGE "ThisWorkbookDataModel";"[Monatsliste].[Monatsnamen].[All].Children" im Tabellenblatt  per Formel
ansprechen zu können, hat mich weiter zu der Überlegung gebracht, ob es in VBA eine Möglichkeit gibt, ein Array mit dieser CUBEMENGE zu befüllen.

Ich denke, die Themenerweiterung passt noch zum obigen Betreff, weshalb ich diesen Thread damit weiterführe.
So in etwa würde ich mir so einen Ablauf vorstellen:

Code:

Sub Daten()

Dim arr As Variant
Dim cbm As CubeField
Dim k As Long

Set cbm = ThisWorkbook.DataModel.ModelTables.Item("[Monatsliste].[Monatsnamen].[All].Children")

ReDim arr(1 To cbm.Items.Count, 1 To 1)

    For k = 1 To UBound(arr(, 1))
        arr(k, 1) = cmb.Items(k).Value
    Next k

End Sub

:92:

Ich würde mich freuen, wenn mir hierzu noch jemand eine Idee liefern könnte.
Danke
Gruß Ludwig

[ruppy]

Habe von VBA nicht wirklich Ahnung, aber zumindest ein bisschen von Cube-Funktionen.

Hatte vor kurzem auch ein Problem mit der Kombi von VBA und Cube Funktionen. 

Das Problem ist das Updaten der Formeln. Cubeformeln reagieren nicht darauf, wenn in VBA Calculate bzw. Calculate.ActiveSheet ausgeführt wird(sorry falls die Begriffe nicht exakt stimmen). Das hat scheinbar was mit asynchroner Aktualisierung zu tun. Kann aber auch sein, dass das nur der Fall ist wenn ein OLAP Cube im Spiel ist und bei lokalen Datenmodell kein Thema ist.

Bzgl. den Arrays: Würde ich nur machen, wenn es unbedingt sein muss. Ist syntaktische echt eklig und findet sich auch auf Google nicht viel dazu.

Übergeb lieber der Funktion Cubewert mehrere Cubeelemente. Erschien mir einfacher.

Cube Funltionen sind generell so eine Sache. Finde die Idee super (PIVOTDATENZUORDNEN könnte man in diesen Fällen quasi ersetzen), aber halte sie alle nicht wirklich für stabil. Irgendwie buggy. 

Fände sie besonders cool, um Abfragen gegen OLAP Cubes auszuführen, aber wie gesagt eher kacke. Glaube es ist besser den Cube umzubauen oder vllt. sogar MDX zu nutzen (nur OLAP).

Kurzum: Überlege gut, ob du das wirklich so lösen willst. Ich würde es eher iwie mit PQ & Power Pivot probieren.

Sorry bisschen längerer Post.

BG ruppy

[LB1972]

Hallo ruppy,

danke für deine Einschätzung.
Trotzdem du von der VBA-Lösung eher abrätst, fände ich es interessant zu wissen, wie das als Makrolösung aussehen würde, bzw. ob es über diesen Weg lösbar ist.
Vielleicht ein bisschen "L'art pour l'art" :64:.
Gruß Ludwig