Forum Discussion

MarkBird's avatar
MarkBird
Contributor III
2 years ago

Create a Bi Blend Table without any aggregation

Hi

I am loading data into a Bi Blend table and I don't want any aggregation applied. My understanding from the documentation is that I can use 'SSOnly.' However, whenever I try use this I get an error. Below is the config:

Question 1: Am I doing something wrong in my config or is my understanding of how it works is incorrect?

Another idea of mine is to set the Blend Unit Dimension Token to a UD member which will only ever have 1 member (e.g. UD4 and set the Aggregation Info to 'None'), so the aggreation should never create any additional aggregated parent records. I have tested this and it appears to work but I am worried that there is a potential issue that I'm not aware of...

Question 2: Is this a viable workaround and is there anything that I should be worried about if I follow this approach?

 

Thanks,

Mark

 

 

Analytic Blend
  • JackLacava's avatar
    JackLacava
    Honored Contributor
    2 years ago

    SSOnly is for when you want to generate the extra Star-Schema table.

    If you simply want to avoid aggregation, leave the aggregation clause of the Blend Unit dimension as Not Used. If that doesn't work for some reason, you can just set "YourTopMemberName;SSOnly", which will generate records only for "YourTopMemberName" and for the base members under it - as well as creating the extra Star-Schema table; you'd then filter out the top records at reporting time. (Note that if you set *any* option in Aggregation clauses, you have to start the clause with the top member of the slice of hierarchy you want to use; you can't just use qualifiers like "SSOnly" on their own.)

    As for issues with your "one-member-dimension" hack: the problem with that is that it nukes any parallelization opportunity for BiBlend, slowing down things significantly. BiBlend parallelizes its workload by dividing it across members of the Blend Unit; no members, no parallelization. This is also why we tend to recommend to use the largest dimensions as Blend Unit, because they will generate more parallelization and hence complete faster.

     

     

    • MarkBird's avatar
      MarkBird
      Contributor III
      2 years ago

      Thanks Jack.

      When I try "NotUsed" or "TopNode;SSOnly" I get the following error:

      I've also tried "TopMember;Member" which nearly worked... As it keeps the base records in sEntity column and then the top member in the Entity but the problem is that it negates my entity mapping. 

      Looks like I'm just going to have to filter out the parent records when retrieving data, but it is just frustrating cause it unnecessarily doubles the number of records in the table. Esp as the aggregated totals are absolutely useless because the aggregation ignores currency conversion etc.

      I think I'll put it in the idea stream... Is it possible to move this to the idea stream or should create a new post?

      • JackLacava's avatar
        JackLacava
        Honored Contributor
        2 years ago

        I would create a new post, because you might want to articulate the use case a bit differently. BiBlend is fundamentally an aggregation engine, not just a data pipe; trying to not do any aggregation at all is kinda forcing it to go against its nature, so you might want to explain why you'd want it to behave like that.

        Btw it's not correct that "the aggregated totals are absolutely useless because the aggregation ignores currency conversion etc". BIBlend does do currency conversion, although it's limited to basic conversion through a single currency. The results will be different from whatever you get through the Cube, for sure (because it doesn't do elimination etc), but again it's meant to be a somewhat simpler aggregation engine that can work on any dimension and on very large amounts of data, so there are some trade-offs involved.