Page 1 of 1

TI views using alternate hierarchies

Posted: Wed Jun 12, 2019 10:15 pm
by PavoGa
Is it not possible to build a view in a TI where one dimension is using elements that exist only in the alternate hierarchy?

We have some consolidations that only exist in an alternate hierarchy. As there is apparently no way on God's green Earth I have found to assign a subset created with HierarchySubsetCreate to a view , I tried using ViewCreateByMDX including those elements and read it in the DATA procedure. The MDX works in PAX just fine, but the TI returns a message it is unable to create the view. The alternate hierarchy is on the [Organization] dimension.

Code: Select all

SELECT 
    non empty {
        [stage.Employee Actuals Measure].[stage.Employee Actuals Measure].[Actual Hours], 
        [stage.Employee Actuals Measure].[stage.Employee Actuals Measure].[Total Labor Cost], 
        [stage.Employee Actuals Measure].[stage.Employee Actuals Measure].[Allowable Labor Cost]
        } 
    ON COLUMNS,
    non empty 
        TM1SubsetToSet([Organization].[ORGPG.001], "Planning Components") *
        {[Fiscal Period].[Fiscal Period].[2019].children} *
        {[HR Level].[HR Level].[All Staff HR Levels].children}
    ON ROWS
FROM [stage.Employee Actuals]
WHERE ([sys.Cube.Adjustments].[sys.Cube.Adjustments].[Effective],
    [Accounts Master].[Accounts Master].[All Accounts],
    [Employee].[Employee].[All Employees],
    [GLC].[GLC].[All GLCs],
    [Sub Period].[Sub Period].[All Sub Periods])
So we decided to do a simpler version on a smaller cube. In this query, we just substitute a single consolidation from the alternate hierarchy. Interestingly, it runs, seems to complete building the hierarchy and start the DATA procedure, but then it crashes the server. The view is not created and saved on the server.

Code: Select all

SELECT non empty  {[bpc.Plan Summary Measure].[bpc.Plan Summary Measure].[Effective]} on 0 ,
      non empty {[Organization].[ORGPG.001].[Core CON]} *
       {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Accounts Master] )}, 0)} *
         {{[Fiscal Period].[2020].Children} + {[Fiscal Period].[Plan Out Year]}}     		on 1
      FROM [bpc.Plan Summary] WHERE ([Version].[Active_01])
      
Both of these MDX statements run fine in PAX and render the desired view. If what I'm attempting is not possible, then it seems the usefulness of the alternate hierarchy functionality is seriously diminished with regards to its promise. :? :cry: :x

Re: TI views using alternate hierarchies

Posted: Thu Jun 13, 2019 7:24 am
by Steve Rowe
Yup, that's my understanding too I'm afraid...Hierarchies don't work with traditional views / subsets and so can't be used as a datasource...

Frustrating that the functionality is not finished...

Re: TI views using alternate hierarchies

Posted: Thu Jun 13, 2019 8:22 am
by lotsaram
Alternate hierarchies don't work with trad views (and never will).

AFAIK if the source of a TI is "subset" then this works just fine with an alternate hierarchy. You just need to set DatasourceNameForServer = 'dimension:hierarchy'

So if you want to use hierarchies in a view as a source for TI then you have to use a MDX view. But this should not crash the server! So you should definitely raise a PMR.

There are a few things not finished like ViewZeroOut not yet working for MDX views (I hear it's coming in 2.0.8).

BTW the next PA "Ask me Anything" session is TODAY. It is worth joining

Re: TI views using alternate hierarchies

Posted: Thu Jun 13, 2019 8:40 am
by Alan Kirk
lotsaram wrote: Thu Jun 13, 2019 8:22 am
BTW the next PA "Ask me Anything" session is TODAY. It is worth joining
It's around 1am my time.

I think that works better for me and better for them because the only thing I would want to "A" in an AMA is "When are you at IBM going to get your collective head out of your backside and start providing a one click installation that actually frapping works, you know, LIKE ALL OF YOUR GODDAMN COMPETITORS DO, because I have had it up to here {gestures to neck} with your Docker bulls**t".

That is the Q to which it seems there will never be an acceptable A. For me it makes any discussion of hierarchies academic.

At least with regard to TM1, it does. But then, TM1 is not the only game in town.

Re: TI views using alternate hierarchies

Posted: Thu Jun 13, 2019 6:25 pm
by PavoGa
Not sure if a mea culpa is due here or not, but views built with MDX are working fine in a TI using leaf elements or consolidations that only exist in an alternate hierarchy. Between interruptions today and asking the same question three times of IBM this morning :roll: ( thanks Lotsaram for the link), I tested using a view built with MDX of the following scenarios:
  1. Added a leaf element to an alternate hierarchy, TI worked fine and output records.
  2. Added a component to the same alternate hierarchy. TI again functioned properly.
  3. Uncommented the offending query that was causing the server crash yesterday and it (LO and BEHOLD!) worked fine (this query uses a set of consolidations from the alternate hierarchy). Up until today, our MDX queries were only using leaf elements which existed in both the alternate hierarchy and the dimname:dimname)
  4. Compared the now working query to the query I posted here last night. No observed differences.
  5. To be certain, copied the query posted here last night into the TI, and it ran with no problems.
This thing was crashing last night. I have witnesses. Right now, we are shaking our heads at it. However, the good news is, building MDX views work around the limitations of ViewSubsetAssign and maybe it was just something in my MDX that was somehow fixed and didn't know it. Or TM1 simply got tired of being crashed and healed itself. :D

PS - Still having problems with StrToMember in a query for CreateViewByMDX. Does not crash the server, it just reports invalid MDX. :(

Re: TI views using alternate hierarchies

Posted: Wed Jun 19, 2019 12:48 pm
by danz
Alan Kirk wrote: Thu Jun 13, 2019 8:40 am
lotsaram wrote: Thu Jun 13, 2019 8:22 am
BTW the next PA "Ask me Anything" session is TODAY. It is worth joining
Hi Alan

I have a REST API question and it relates to one of your post (which the Reply function is locked), what is the best way to ask you? I have tried sending IM from back end to Site Admin but didn't seem to get to you.

Thanks in advance.
Dan