TI views using alternate hierarchies
Posted: 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.
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.
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.
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])
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])