将多维SSAS移植到ICCube. Scope()是否等效?其他差距/问题? [英] porting multidimensional SSAS to ICCube. Scope() equivalent? other gaps/issues?

问题描述

有人曾经将复杂的ssas多维多维数据集移植到iccube吗?并就这两种工具之间的经验教训/差距有什么建议吗?

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?

我可以看到的主要对象是scope(). iccube中的等效功能是什么?嵌套的if/case语句?

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

我们有一个非常重要的mdx脚本计算密集型立方体，而SSAS计算引擎的单线程本质是一个真正的瓶颈.我们看过的其他olap工具都没有足够快或足够丰富的语言

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

更新1:我们如何使用范围的简单示例. ic，您提到存在可靠"的解决方法.这样的代码会是什么?

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;

这样做的好处是默认情况下complexcalc为null.它只有在满足特定条件时才会获得值.使用范围的主要原因是为了提高速度.比if/case块要快得多(并且更容易理解) 我不需要明确定义哪些基准有效，只是哪些基准无效.

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.

及以下是我们实现日期实用程序维度的方式.它允许我们做类似的事情 (度量值，[昏暗日期计算].[日期计算].[先前期间])，它为昏暗日期的当前成员提供了先前的度量标准期间(月份返回1个月，季度返回3个月，学期返回返回6个月，年份返回12个月).它非常干净，准确且非常快速.

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;

推荐答案

[免责声明我正在为icCube工作]

[disclaimer I'm working for icCube]

范围不是icCube的一部分，尚未计划.这是一个棘手的功能，它自然不适合icCube的体系结构(请参阅下面的讨论，稍后...). icCube的实力也是其研发团队的敏捷性，请随时与他们联系，他们将非常乐于改进和添加功能.

Scope is not part of icCube, not yet planned. It's a tricky feature that didn't naturally fit into icCube's architecture (see discussion below, later ... ). The strength of icCube is also the agility of it's R&D team, do not hesitate to contact them directly they will me more than happy to improve and add features.

在icCube中，有些功能可能与经典的MDX服务器不同，它们可能是有用的，它们是Categorys，SubCubes和eval函数.

In icCube there are some functionalities that are different from classical MDX server that might be useful, it's Categories, SubCubes and the eval function.

类别.允许定义行为类似于经典成员的新成员.可以将新成员定义为一组成员或子多维数据集.例如，在这里我们可以将[Top10]类别成员定义为我们的10个最重要的客户:

Categories. Allows for defining a new member that behaves like classical members. This new member can be defined as a set of members or as a subcube. For example here we can define a [Top10] category member as our 10 most important customers :

 CATEGORY MEMBER [Top10] as TopCount( [Customers], 10, ([Measures].[Sales],[2015]) )

 -> so later on ( [Top10], [Sales] ) will return the sales of this top10 customers

SubCubes允许将成员上更丰富的逻辑关系定义为一组元组. icCube实现 SubCubeComplement ， SubCubeSymDifference ， SubCubeUnion 和

SubCubes, allows defining richer logical relations on members as a set of tuples. icCube implements SubCubeComplement, SubCubeIntersect,SubCubeOthers, SubCubeSymDifference, SubCubeUnion and SubCubeMinus. So calculating all without France (it's trivial here, but think on hierachies with many-to-many relations)

  SubCubeMinus([Geography].[Geo].[All], [Geography].[Geo].[France] )

Eval 函数，用于评估子多维数据集上的表达式.这是一个使用联合执行求和的简单示例:

The Eval function, allows for evaluating an expression on a subCube. Here is a trivial example doing the sum using the union :

 MEMBER [US+CH] AS Eval( SubCubeUnion( [Switzerland], [United States]) , [Amount])

最后但并非最不重要的一点是，您可以为dates函数定义 Function 在icCube中，您可以在MDX中重复使用，而无需在任何地方复制和粘贴:

Last but not least, for the dates function you can define Function in icCube that you can reuse in your MDX, no need to copy&paste everywhere :

 CREATE FUNCTION square( Value val ) AS val * val

并与 CompactSet 结合使用，可以更快地评估日期( (如果在此维度上没有m2m关系)或调用某些 Java函数 (您必须激活默认关闭的此模块).

and combine with CompactSet for a faster evaluation on dates periods (if there are no m2m relations on this dimension) or call some Java functions (you've to activate this module that is off by default).

---------------------范围------------------------- -

--------------------- Scope ---------------------------

警告:评论可能已过时，因为我对范围的了解是几年前的事情之一.我们走吧:

Warning : Comments might be obsolete as my understanding of scope is the one of a few years ago. Let's go :

Scope是一个很好的功能，但是您可以在Chris Webb的演示文稿中查看一些缺点(链接)，从47:30开始检查大约5分钟.

Scope is a nice functionality but there are some drawbacks that you can check in Chris Webb's presentation ( link ), check from 47:30 for about 5 minutes.

问题所在/所在位置:

以某种方式，范围允许为子多维数据集定义一个新值(请记住，子多维数据集可能是单个不可分割的单元格，也可能是成千上万个不可分割的单元格)

Somehow a scope allows to define a new value for a subcube (remember a subcube might be a single indivisible cell as well as thousands of them)

1)范围允许定义子多维数据集的值，但是如果您想要该子多维数据集的一部分"怎么办?

1) Scopes allows for defining the value of a subcube but what do you do if you want a 'part' of this subcube ?

2)如果两个合并范围发生冲突(交集不为空)会发生什么?

2) What happens if two scopes collide (intersection is not empty) ?

所有这些都与安全性，多对多关系，子查询和set where子句混合在一起.

And all this mixed with security, many-to-many relations, subqueries and set where clauses.

我们不是SSAS专家，有可能对我们有更好的了解，然后再次尝试实施一个干净的解决方案，但就我们所知，我们相信还有其他方法可以解决问题(例如，使用计算成员或写回).

We're not SSAS specialist and it's possible that with a better understanding we try again to implement a clean solution but for know we believe there are other ways solving the problems (for example using calc. members or writebacks).

希望有帮助.

这篇关于将多维SSAS移植到ICCube. Scope()是否等效?其他差距/问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！