Skip to content

Instantly share code, notes, and snippets.

@prateeka
Created November 27, 2020 06:25
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save prateeka/5f0b49811cd704e30bcd9603b5a17637 to your computer and use it in GitHub Desktop.
Save prateeka/5f0b49811cd704e30bcd9603b5a17637 to your computer and use it in GitHub Desktop.
virtualCube for firstChild calculating measure at "targetLevel" instead of "firstChild" level
OrderCube(
AggregateLogical(
SelectedData(
ColumnData(SelectColumn(Some(c0),AttributeValue(FlatAttribute(62874bbe-7ead-48ac-4496-8c7758b5aaa8: "orderquantity1")),Sum), Sum, Some(c0)),
ColumnData(SelectColumn(Some(c1),DimensionallyModifiedValue(AggregatedValue(AttributeValue(FlatAttribute(62874bbe-7ead-48ac-4496-8c7758b5aaa8: "orderquantity1")),Sum),List(Child(CurrentMember(FlatHierarchy(9bf44374-c106-4ab3-9c6a-d68474e9b25f+3938: "Product Dimension")),true))),NoAggregation), NoAggregation, Some(c1)),
ColumnData(SelectColumn(Some(c2),AttributeValue(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line")),AggregateGrouped(true)), AggregateGrouped(true), Some(c2)),
ColumnData(SelectColumn(Some(c3s0),KeyValue(FlatKey(3b0e5176-ca31-4706-a508-092e3c17daa2)),AggregateGrouped(true)), AggregateGrouped(true), Some(c3s0))
),
AuxiliaryData(),
UnionCubes(
AggregatePlanned(
SelectedData(
ColumnData(SelectColumn(Some(c0),AttributeValue(FlatAttribute(62874bbe-7ead-48ac-4496-8c7758b5aaa8: "orderquantity1")),Sum), Sum, Some(c0)),
NullData(DimensionallyModifiedData(ValueData(AggregatedValue(AttributeValue(FlatAttribute(62874bbe-7ead-48ac-4496-8c7758b5aaa8: "orderquantity1")),Sum),NoAggregation,Set(RawAttribute(FlatAttribute(62874bbe-7ead-48ac-4496-8c7758b5aaa8: "orderquantity1"),Sum,Some(c_50))),Some(c_51)),List(Child(CurrentMember(FlatHierarchy(9bf44374-c106-4ab3-9c6a-d68474e9b25f+3938: "Product Dimension")),true)),Min,Some(c_51))),
ColumnData(SelectColumn(Some(c2),AttributeValue(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line")),AggregateGrouped(true)), AggregateGrouped(true), Some(c2)),
ColumnData(SelectColumn(Some(c3s0),KeyValue(FlatKey(3b0e5176-ca31-4706-a508-092e3c17daa2)),AggregateGrouped(true)), AggregateGrouped(true), Some(c3s0))
),
AuxiliaryData(),
ConstrainCube(
RawCube(Cube(b89a2fb7-74f4-4828-706e-70f7186e10a0: "Internet Sales Cube")),
Constraints(
ValueConstraint(ConstantValue[BooleanType](true)),
ValueConstraint(ConstantValue[BooleanType](true))
)
)
),
JoinCubes(
JoinCubes(
JoinCubes(
AggregatePlanned(
SelectedData(
RawKey(FlatKey(3b0e5176-ca31-4706-a508-092e3c17daa2),1,AggregateGrouped(true),Some(c_65)),
RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),1,AggregateGrouped(true),Some(c_66)),
RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),2,AggregateGrouped(true),Some(c_67)),
ValueData(WindowFunctionApplication(RankWindowFunction,List(),List(),List((AttributeValue(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line")),FlatAscending)),None),NoAggregation,Set(RawAttribute(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line"),AggregateGrouped(true),Some(c_63))),Some(rk_c64)),
RawAttribute(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line"),AggregateGrouped(true),Some(c_68))
),
AuxiliaryData(),
RawCube(Cube(b89a2fb7-74f4-4828-706e-70f7186e10a0: "Internet Sales Cube"))
),
AggregatePlanned(
SelectedData(
ModifiedData(RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),1,AggregateGrouped(true),Some(c_72)),CurrentMember(FlatHierarchy(9bf44374-c106-4ab3-9c6a-d68474e9b25f+3938: "Product Dimension")),RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),1,AggregateGrouped(true),Some(c_72)),Some(c_74)),
ModifiedData(RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),2,AggregateGrouped(true),Some(c_73)),CurrentMember(FlatHierarchy(9bf44374-c106-4ab3-9c6a-d68474e9b25f+3938: "Product Dimension")),RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),2,AggregateGrouped(true),Some(c_73)),Some(c_75)),
ValueData(WindowFunctionApplication(RowNumberWindowFunction,List(),List(),List((AttributeValue(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line")),FlatAscending), (AttributeValue(FlatAttribute(aa282783-d9e3-45ae-646d-f075873f8620: "Product Category")),FlatAscending)),None),NoAggregation,Set(RawAttribute(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line"),AggregateGrouped(true),Some(c_69)), RawAttribute(FlatAttribute(aa282783-d9e3-45ae-646d-f075873f8620: "Product Category"),AggregateGrouped(true),Some(c_70))),Some(rn_c71)),
RawAttribute(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line"),AggregateGrouped(true),Some(c_76)),
RawAttribute(FlatAttribute(aa282783-d9e3-45ae-646d-f075873f8620: "Product Category"),AggregateGrouped(true),Some(c_77))
),
AuxiliaryData(),
RawCube(Cube(b89a2fb7-74f4-4828-706e-70f7186e10a0: "Internet Sales Cube"))
),
JoinSelectedData(
DerivedData(RawKey(FlatKey(3b0e5176-ca31-4706-a508-092e3c17daa2),1,AggregateGrouped(true),Some(c_65)),com.atscale.engine.core.plan.cube.JoinLeft$@4478b11e,Some(AggregateGrouped(true)),None),
DerivedData(ModifiedData(RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),1,AggregateGrouped(true),Some(c_72)),Child(CurrentMember(FlatHierarchy(9bf44374-c106-4ab3-9c6a-d68474e9b25f+3938: "Product Dimension")),true),ModifiedData(RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),1,AggregateGrouped(true),Some(c_72)),CurrentMember(FlatHierarchy(9bf44374-c106-4ab3-9c6a-d68474e9b25f+3938: "Product Dimension")),RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),1,AggregateGrouped(true),Some(c_72)),Some(c_74)),Some(c_78)),com.atscale.engine.core.plan.cube.JoinRight$@1a6824aa,Some(AggregateGrouped(true)),None),
DerivedData(ModifiedData(RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),2,AggregateGrouped(true),Some(c_73)),Child(CurrentMember(FlatHierarchy(9bf44374-c106-4ab3-9c6a-d68474e9b25f+3938: "Product Dimension")),true),ModifiedData(RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),2,AggregateGrouped(true),Some(c_73)),CurrentMember(FlatHierarchy(9bf44374-c106-4ab3-9c6a-d68474e9b25f+3938: "Product Dimension")),RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),2,AggregateGrouped(true),Some(c_73)),Some(c_75)),Some(c_79)),com.atscale.engine.core.plan.cube.JoinRight$@1a6824aa,Some(AggregateGrouped(true)),None)
),
JoinConstraints(
CubeDataConstraint(DerivedData(ValueData(WindowFunctionApplication(RankWindowFunction,List(),List(),List((AttributeValue(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line")),FlatAscending)),None),NoAggregation,Set(RawAttribute(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line"),AggregateGrouped(true),Some(c_63))),Some(rk_c64)),com.atscale.engine.core.plan.cube.JoinLeft$@4478b11e,None,None),None,DerivedData(ValueData(WindowFunctionApplication(RowNumberWindowFunction,List(),List(),List((AttributeValue(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line")),FlatAscending), (AttributeValue(FlatAttribute(aa282783-d9e3-45ae-646d-f075873f8620: "Product Category")),FlatAscending)),None),NoAggregation,Set(RawAttribute(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line"),AggregateGrouped(true),Some(c_69)), RawAttribute(FlatAttribute(aa282783-d9e3-45ae-646d-f075873f8620: "Product Category"),AggregateGrouped(true),Some(c_70))),Some(rn_c71)),com.atscale.engine.core.plan.cube.JoinRight$@1a6824aa,None,None),None,EqualsComparison)
),
InnerJoin
),
AggregatePlanned(
SelectedData(
RawKey(FlatKey(3b0e5176-ca31-4706-a508-092e3c17daa2),1,AggregateGrouped(true),Some(c_80)),
RawAttribute(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line"),AggregateGrouped(true),Some(c_52)),
RawKey(FlatKey(3b0e5176-ca31-4706-a508-092e3c17daa2),1,AggregateGrouped(true),Some(c_53))
),
AuxiliaryData(),
RawCube(Cube(b89a2fb7-74f4-4828-706e-70f7186e10a0: "Internet Sales Cube"))
),
JoinSelectedData(
DerivedData(RawKey(FlatKey(3b0e5176-ca31-4706-a508-092e3c17daa2),1,AggregateGrouped(true),Some(c_65)),com.atscale.engine.core.plan.cube.JoinLeft$@4478b11e,Some(AggregateGrouped(true)),Some(c_81)),
DerivedData(ModifiedData(RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),1,AggregateGrouped(true),Some(c_72)),Child(CurrentMember(FlatHierarchy(9bf44374-c106-4ab3-9c6a-d68474e9b25f+3938: "Product Dimension")),true),ModifiedData(RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),1,AggregateGrouped(true),Some(c_72)),CurrentMember(FlatHierarchy(9bf44374-c106-4ab3-9c6a-d68474e9b25f+3938: "Product Dimension")),RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),1,AggregateGrouped(true),Some(c_72)),Some(c_74)),Some(c_78)),com.atscale.engine.core.plan.cube.JoinLeft$@4478b11e,Some(AggregateGrouped(true)),Some(c_82)),
DerivedData(ModifiedData(RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),2,AggregateGrouped(true),Some(c_73)),Child(CurrentMember(FlatHierarchy(9bf44374-c106-4ab3-9c6a-d68474e9b25f+3938: "Product Dimension")),true),ModifiedData(RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),2,AggregateGrouped(true),Some(c_73)),CurrentMember(FlatHierarchy(9bf44374-c106-4ab3-9c6a-d68474e9b25f+3938: "Product Dimension")),RawKey(FlatKey(b75e5589-2223-4250-ac17-ff7b86120852),2,AggregateGrouped(true),Some(c_73)),Some(c_75)),Some(c_79)),com.atscale.engine.core.plan.cube.JoinLeft$@4478b11e,Some(AggregateGrouped(true)),Some(c_83)),
DerivedData(RawAttribute(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line"),AggregateGrouped(true),Some(c_52)),com.atscale.engine.core.plan.cube.JoinRight$@1a6824aa,Some(AggregateGrouped(true)),Some(c_85))
),
JoinConstraints(
CubeDataConstraint(DerivedData(RawKey(FlatKey(3b0e5176-ca31-4706-a508-092e3c17daa2),1,AggregateGrouped(true),Some(c_59)),com.atscale.engine.core.plan.cube.JoinLeft$@4478b11e,None,None),None,DerivedData(RawKey(FlatKey(3b0e5176-ca31-4706-a508-092e3c17daa2),1,AggregateGrouped(true),Some(c_80)),com.atscale.engine.core.plan.cube.JoinRight$@1a6824aa,None,None),None,EqualsComparison)
),
InnerJoin
),
AggregatePlanned(
SelectedData(
ValueData(AggregatedValue(AttributeValue(FlatAttribute(62874bbe-7ead-48ac-4496-8c7758b5aaa8: "orderquantity1")),Sum),NoAggregation,Set(RawAttribute(FlatAttribute(62874bbe-7ead-48ac-4496-8c7758b5aaa8: "orderquantity1"),Sum,Some(c_50))),Some(c_51)),
ColumnData(SelectColumn(Some(c2),AttributeValue(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line")),AggregateGrouped(true)), AggregateGrouped(true), Some(c2)),
ColumnData(SelectColumn(Some(c3s0),KeyValue(FlatKey(3b0e5176-ca31-4706-a508-092e3c17daa2)),AggregateGrouped(true)), AggregateGrouped(true), Some(c3s0)),
RawKey(FlatKey(3b0e5176-ca31-4706-a508-092e3c17daa2),1,AggregateGrouped(true),Some(c_59))
),
AuxiliaryData(),
RawCube(Cube(b89a2fb7-74f4-4828-706e-70f7186e10a0: "Internet Sales Cube"))
),
JoinSelectedData(
NullData(ColumnData(SelectColumn(Some(c0),AttributeValue(FlatAttribute(62874bbe-7ead-48ac-4496-8c7758b5aaa8: "orderquantity1")),Sum), Sum, Some(c0))),
DimensionallyModifiedData(ValueData(AggregatedValue(AttributeValue(FlatAttribute(62874bbe-7ead-48ac-4496-8c7758b5aaa8: "orderquantity1")),Sum),NoAggregation,Set(RawAttribute(FlatAttribute(62874bbe-7ead-48ac-4496-8c7758b5aaa8: "orderquantity1"),Sum,Some(c_50))),Some(c_51)),List(Child(CurrentMember(FlatHierarchy(9bf44374-c106-4ab3-9c6a-d68474e9b25f+3938: "Product Dimension")),true)),Min,Some(c_51)),
ColumnData(SelectColumn(Some(c2),AttributeValue(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line")),AggregateGrouped(true)), AggregateGrouped(true), Some(c2)),
ColumnData(SelectColumn(Some(c3s0),KeyValue(FlatKey(3b0e5176-ca31-4706-a508-092e3c17daa2)),AggregateGrouped(true)), AggregateGrouped(true), Some(c3s0))
),
JoinConstraints(
CubeDataConstraint(DerivedData(ModifiedData(RawKey(FlatKey(3b0e5176-ca31-4706-a508-092e3c17daa2),1,AggregateGrouped(true),Some(c_86)),Child(CurrentMember(FlatHierarchy(9bf44374-c106-4ab3-9c6a-d68474e9b25f+3938: "Product Dimension")),true),RawKey(FlatKey(3b0e5176-ca31-4706-a508-092e3c17daa2),1,AggregateGrouped(true),Some(c_86)),None),com.atscale.engine.core.plan.cube.JoinLeft$@4478b11e,None,None),None,DerivedData(RawKey(FlatKey(3b0e5176-ca31-4706-a508-092e3c17daa2),1,AggregateGrouped(true),Some(c_86)),com.atscale.engine.core.plan.cube.JoinRight$@1a6824aa,None,None),None,EqualsComparison)
),
InnerJoin
),
UnionSelectedData(
ColumnData(SelectColumn(Some(c0),AttributeValue(FlatAttribute(62874bbe-7ead-48ac-4496-8c7758b5aaa8: "orderquantity1")),Sum), Sum, Some(c0)),
DimensionallyModifiedData(ValueData(AggregatedValue(AttributeValue(FlatAttribute(62874bbe-7ead-48ac-4496-8c7758b5aaa8: "orderquantity1")),Sum),NoAggregation,Set(RawAttribute(FlatAttribute(62874bbe-7ead-48ac-4496-8c7758b5aaa8: "orderquantity1"),Sum,Some(c_50))),Some(c_51)),List(Child(CurrentMember(FlatHierarchy(9bf44374-c106-4ab3-9c6a-d68474e9b25f+3938: "Product Dimension")),true)),Min,Some(c_51)),
ColumnData(SelectColumn(Some(c2),AttributeValue(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line")),AggregateGrouped(true)), AggregateGrouped(true), Some(c2)),
ColumnData(SelectColumn(Some(c3s0),KeyValue(FlatKey(3b0e5176-ca31-4706-a508-092e3c17daa2)),AggregateGrouped(true)), AggregateGrouped(true), Some(c3s0))
)
)
),
OrderBy(
ColumnOrdering(SelectColumn(Some(c2),AttributeValue(FlatAttribute(2ff18d3a-894a-4be1-6d5c-39d97245160a: "Product Line")),AggregateGrouped(true)),FlatAscending)
)
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment