TI views using alternate hierarchies

Post Reply
User avatar
PavoGa
Community Contributor
Posts: 367
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

TI views using alternate hierarchies

Post by PavoGa » Wed Jun 12, 2019 10:15 pm

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
Ty
Cleveland, TN

User avatar
Steve Rowe
Site Admin
Posts: 1956
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: TI views using alternate hierarchies

Post by Steve Rowe » Thu Jun 13, 2019 7:24 am

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...

lotsaram
MVP
Posts: 3303
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TI views using alternate hierarchies

Post by lotsaram » Thu Jun 13, 2019 8:22 am

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
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
Alan Kirk
Site Admin
Posts: 5989
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: TI views using alternate hierarchies

Post by Alan Kirk » 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
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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
PavoGa
Community Contributor
Posts: 367
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: TI views using alternate hierarchies

Post by PavoGa » Thu Jun 13, 2019 6:25 pm

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. :(
Ty
Cleveland, TN

danz
Posts: 6
Joined: Mon Jun 10, 2019 2:47 am
OLAP Product: TM1
Version: PA2
Excel Version: Office365

Re: TI views using alternate hierarchies

Post by danz » Wed Jun 19, 2019 12:48 pm

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

Post Reply