Lol... So far so good.. both the design and our 'friend' 😛

I want to discuss that with you... how about if get you the schema of our data mart? Are...

I solved it, partitioning on FullyQualifiedName and OrgEntityID worked.

my bad; its calculating the Levels incorrectly.

Jeff:

its not calculating N correctly for some of the recurring entityIDs though I have extended the type along with fullyqualifiedname. I am in the mid of a deployment and...

Um..... I have been pondering over it too.. what design would you propose keeping in view that the Cube will be processed on the data mart with the current design...

Yo nailed it.. here's what I did

I have joined EntityType with FullyQualifiedName. The script would treat it as another extension to levels. My last select remains intact and I get...

Jeff:

Each row is unique in the data set. EntityID may occur twice but it will form a unique row with Type. As far as its usage goes, if you...

Those unused columns were just for the purpose of dev check; I am only keeping FullyQualifiedName, Rownum and EntityID.

Duplicates records need to be there as they'll be going in...

Those unused columns were just for the purpose of dev check; I am keeping FullyQualifiedName, Rownum and EntityID only.

Duplicates records need to be there as they'll be going in...

Jeff; I added RowNum in #Hie itself and removed the first CTE, works well within just under 2 minutes, Thumbs up SIR; just one last thing, you did mention it...

This is what I have done; plugged the tally from the snippet for #a;

;WITH

ctePreNumber AS

( --=== Add a row number to each "a" so we can repivot after the split

...

It ain't working on #HIE 🙁 either its too much of data or what.. it keeps executing freezing my notebook :S

Um; I had dynamic SQL in my mind after a glimpse of this query; however I'll go through the article and get back to you on this. I'll go catch...

Thank you, thank you so much, it did work with #a I'll try with #Hie too. I'll keep you posted on it. Once I am done with the deployment I'll...

Please take your time I am here...b/w, I am going to apply this thin on #HIE or the CTE , just doing a dry run on #a with 3 records.

~...