Has anybody ever ported a complex ssas multidmensional cube to iccube? and have any advice to offer on lessons learnt/gaps between the two tools etc?
The major one i can see is scope(). What's the equivalent in iccube? nested if/case statements?
I have a list here. Anything else?
function | SSAS | iccube
------------------------|------------------------|------------------------
multi threaded calcs | no | yes
------------------------|------------------------|------------------------
fix/scope block code | SCOPE() | ??
------------------------|------------------------|------------------------
custom functions | clr.net but it's slow | mdx+
------------------------|------------------------|------------------------
generic date utility | third-party code that | ??
dimensions (eg generic | uses scope() eg |
prior period/prior | datetool |
corresponding period) | |
------------------------|------------------------|------------------------
We have a very mdx script calculation heavy cube, and the single threaded nature of the SSAS calculation engine is a real bottleneck. None of the other olap tools we've looked at have been fast enough or had a rich enough language
We use disconnected utility dimensions to drive the functionality, and need to have a date utility dimension (we use a version of this http://sqlmag.com/sql-server-analysis-services/optimizing-time-based-calculations-ssas), use AXIS() extensively, and have recursive sum product across descendents of a hierarchy for a non-additive measure.
Our cube isn't a self service reporting cube. It's a multidimensional calculation engine for our app with a fixed generic schema
Update 1: A simpler example of how we use scope. ic, You mention 'robust' workarounds exist. What would they be for code like this?
// Assumes the date utility dim has been setup as with the priorperiod function as [Dim Date Calculations].[Date Calculations].[Prior Period]
// DimBenchmark is a single attribute disconnected utility dimension. The initial/default value is DimBenchmark.Benchmark.None ie do nothing. The remainder are dynamically set based on code. hardcoded below for simplicity
Scope(DimBenchmark.BenchMark.PriorPeriod);
THIS = [Dim Date Calculations].[Date Calculations].[Prior Period]; // assign the value of some physical and utility dim members to new benchmark attributes. Allows us to only refer to dimbenchmark in subsequent code, irrespective of number of benchmarks or the src dimension.attribute
END SCOPE;
SCOPE(DimBenchmark.BenchMark.Budget);
THIS = DimScenario.Scenario.Budget; //we also have a budget
END SCOPE;
.... // any number of other benchmarks
Create measure currentcube.measures.ComplexCalc as NULL;
SCOPE (measures.ComplexCalc); // this code will only change how complex calc behaves
SCOPE (DimBenchmark.Benchmark.All - DimBenchmark.Benchmark.None); // this will only change the ComplexCalc when the active benchmark selection is not "none"
this= (some measure,Complex subcube etc);
End Scope;
End Scope;
the benefit of this is that complexcalc is null by default. it only gets a value when it meets specific conditions. the main reason to use scope is for speed. Much faster than if/case blocks (and simpler understand) i dont need to explicitly define which benchmarks are valid, just which benchmark isn't.
and below is how we've implemented the date utility dimension. It allows us to do something like (measure,[Dim Date Calculations].[Date Calculations].[Prior Period]) and it gives use the prior period of measure for the current member of dim date (month goes back 1 month, quarter goes back 3 months, semester goes back 6 mo, year goes back 12 mo). It's very clean, accurate and pretty fast.
-- Fiscal Month
Scope( [Dim Date].[Month Key].[Month Key].members);
-- Prior Period
Scope([Dim Date Calculations].[Date Calculations].[Prior Period]);
this =
( [Dim Date].[Month Key].CurrentMember.PrevMember
,[Dim Date Calculations].[Date Calculations].[Current]
);
END scope;
End Scope;
-- Fiscal Quarter
Scope( [Dim Date].[Fiscal Quarter].[Fiscal Quarter].members);
-- Prior Period
SCOPE( [Dim Date Calculations].[Date Calculations].[Prior Period]);
THIS = ( [Dim Date].[Fiscal Quarter].CurrentMember.PrevMember
,[Dim Date Calculations].[Date Calculations].[Current]
);
END SCOPE;
END SCOPE;
-- Fiscal Semester
Scope( [Dim Date].[Fiscal Semester].[Fiscal Semester].members);
-- Prior Period
SCOPE( [Dim Date Calculations].[Date Calculations].[Prior Period]);
THIS = ( [Dim Date].[Fiscal Semester].CurrentMember.PrevMember
,[Dim Date Calculations].[Date Calculations].[Current]
);
END SCOPE;
End Scope;
-- Fiscal Year
Scope( [Dim Date].[Fiscal Year].[Fiscal Year].members);
-- Prior Period
SCOPE( [Dim Date Calculations].[Date Calculations].[Prior Period]);
THIS =
( [Dim Date].[Fiscal Year].CurrentMember.PrevMember
,[Dim Date Calculations].[Date Calculations].[Current]
);
END SCOPE;
End Scope;
