So I was able to get my problem resolved.

I explained there were two hops from the dimension to the FACT table. The reason I was getting duplicate rows was...

I have had a bit more time to look at this issue and wanted to explain further with some clarity.

In the DSV, the relationship are as below

DimEntOrg-------->DimEntOrg_Acct_Profiles----------->DimAcct_Profiles-------->FACTTABLE

I am querying...

See attached Martin

I have this key directly in the FACT table as well as in the intermediate table (ie acct profile id). I tried it both as a regular and...

Hey Martin

Good Morning. I am current troubleshooting this issue further and have nailed it down to the fact that my .currentmember instead of returning members inside the hierarchy it iterates...

Hey Martin

I appreciate you taking the time to even help with this issue.

I made a few attribute changes to your script and have the following result set attached.

I...

Nada

🙁

Attached output - just so i know im not losing my mind here

Thats totally crazy Martin because it only seems to work with the Date Dimension only in my env.

SELECT

NON EMPTY Hierarchize({DrilldownLevel({[Dim Region].[REGION_HCY].[All]})})

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS

FROM...

Thanks Martin

The behaviour you described is spot on in that without a date dimension on the axo=is, nothing is displayed. The question now is how do I get the values...

Hey Martin

Thanks for your help as always. Something seems to br wrong with the script because I am now replicating the problem in Prod.

So this is what doesnt work

SELECT

{[Measures].[BEGIN_LIS],[Measures].[BEGIN_LIS_tmp]}...

Found a problem in my code.

Syntax is correct for lookup. Dataset was not returning anything to SSRS renderer because I was passing the wrong parameters.

Thanks

Hey Tom

Thanks for getting here. The Cte definitely works for one off queries. This model I am looking to fix however is for a DataWarehouse and I need some sort...

Hey Ron

While this works as a solution for a one off run. This is going to be difficult if not impossible to implement in a model because it means everytime...

Thanks for getting back. Not...

Thanks Martin - for a DW with 5 Million rows, SSAS seems to be handling it quite well. So far, no performance issues noticed (argh - i think i just...

Martin

Spot on - redesigned the composite keys and the relationship (attached). Hierarchies are now fixed. This is awesome.

Thanks for your help as always.

So - I guess just for my curiousity,...