Skip to Content

SAP HANA SP7 has a key feature called STAR JOIN in Calculation View.  This feature allows us to easily Model STAR JOIN when modeling with multiple fact tables where we need measures from multiple fact tables.

What is STAR JOIN:

STAR JOIN is a scenario in a calculation view which will be enabled when you create calculation view with a STAR JOIN option.

Below diagram shows a STAR JOIN having same set of dimension tables around multiple fact tables. Now if we need measures from both Facts (Fact Table-1 & Fact Table-2) we can use STAR JOIN feature in Calculation view.

/wp-content/uploads/2014/02/pic1_387630.png

Present Approach:

One of the ways we handle this scenario while Modeling is to create Attribute views for dimensions and create individual Analytical view for each fact table and link Attribute views and later use the Analytical views as projection in Calculation view and use Join or Union feature to link measure and attributes for view consumption.

STAR JOIN feature:

Star Join feature in SP07 Simplifies the above mentioned process.  The blog explains the design steps for implementation of STAR JOIN in Calculation view.

Below is the SQL. script which was used to create the prototype model. In following script will create two different fact tables and
two different Dimension tables. You could use this script for your testing purpose.

———————  DIM Tables —————————-

/* This Dimension table is Employee name table (haveEmpId and EmpName) */

drop table Empdim;

create column table Empdim (empId nvarchar(3) ,Empname nvarchar(20));

insert into Empdim values(‘A1’,‘Shivaji’);

insert into Empdim values(‘B1’,‘Anand’);

insert into Empdim values(‘C1’,‘Stephan’);

/* This Dimension table is  a calendar table (date,month and year) */

drop table Empdate;

create column table Empdate (caldate  date,CALMONTH nvarchar(4) ,CALYEAR nvarchar(4));

insert into Empdate values(‘20100101’,’01’,‘2010’);

insert into Empdate values(‘20110101’,’02’,‘2011’);

insert into Empdatevalues(‘20120101’,’03’,‘2012’);

——————————————–FACT Tables —————————————-

/* This Fact table describes Employee Salary */

drop table Empfact1;

Create column table Empfact1 (empId nvarchar(3), Empdate date, Sal integer );

insert into  Empfact1 values(‘A1’,‘20100101’,4000);

insert into  Empfact1 values(‘B1’,‘20110101’,6000);

insert into  Empfact1 values(‘C1’,‘20120101’,8000);

/* This Fact table describes Employee Bonus */

drop table Empfact2;

Create column table Empfact2 (empId nvarchar(3), deptName nvarchar(20), Bonus integer );

insert into Empfact2 values(‘A1’,‘SAP’,1000);

insert into Empfact2 values(‘B1’,‘NS2’,2000);

insert into Empfact2 values(‘C1’,‘SAPAG’,3000);

We will design a model where we want to see Employee Salary and Bonus (measures coming from two different Fact Tables) in one view. This can be achieved quite easily with one STAR JOIN Calculation View.

Advantages

Disadvantages

Unknown

Simplify design process (Selection of multiple measures from multiple
  fact tables in one model)

Attribute view, Analytical views or base table cannot be used in STAR
  Join Calculation View

Performance is not tested

3NF design is possible with star Join

Attribute
  views has to be converted as Dimensional Calculation view

Performance is not tested.

NOTE: The purpose of this blog is to highlight thefunctionality of the STAR JOIN and not the performance.

Steps to implement STAR JOIN:

Important points to keep in mind while creating STAR JOIN scnerio in a model:

  • It doesn’t allow base Column tables or Attribute Views or Analytical views.
  • All Dimension tables need to be created as a Calculation View.
  • You can Only Select “Dimension Calculation View” in Star Join Scenario in STAR JOIN Calculation View.
  • With In STAR JOIN Scenario in Calculation View it doesn’t allow joining between calculation views.

A) Create Dimension Calculation Views for dimensions (“EMPDAT_DIM” on “EMPDATE”).

A.1) From the input Select the “Dimension” from the pull down menu while from the Create pop-up screen.

/wp-content/uploads/2014/02/pic2_387638.png

  A.2) Create Dimension Calculation Views for dimensions “EMPDAT_DIM” on “EMPDATE” Dimension table.

/wp-content/uploads/2014/02/pic3_387644.png

  A.3) Create Calculation view EMPLOYEE_DIM on EMPDIM Dimension table.

/wp-content/uploads/2014/02/pic4_387640.png

B) Create a New Star Join Calculation View

B.1) Create a New Calculation view and select options as shown below.

/wp-content/uploads/2014/02/pic5_387645.png

Below are the steps used to create a STAR JOIN Model.(This process is similar to creating an Analytical View)


/wp-content/uploads/2014/02/pic6_387646.png
B.2) SELECT FACT Tables

Here you project two Base Column fact tables (similar way you join multiple tables in DF in Analytical View)

/wp-content/uploads/2014/02/pic7_387597.png


B.3 ) Join Two Fact Tables


/wp-content/uploads/2014/02/pic8_387650.png

B.4) STAR JOIN

/wp-content/uploads/2014/02/pic9_387652.png

C. Activate and Review the data.

Conclusion:

Thanks for reading my blog. Please provide comments and feedback.

To report this post you need to login first.

31 Comments

You must be Logged on to comment or reply to a post.

    1. sidharth mishra

      Hi Anand,

      What about the performance?

      We had serious issues while joining at calculation views and now every join is at Calculation view.

      Moreover there seems to be no use of the Analytic Views if we can do everything up there in calc view.

      Best regards,

      Sidharth Mishra

      (0) 
      1. Kris Claes

        Hi,

        did you ever get an answer to this remark? Or did you find a solution for it?

        I think we have the same issue: the join of an Attribute View and an Analytical View.

        It returns all the lines of the Anal.View (20 mio. or so) taking a lot of time, and then joins it with the Attr.View. It should select the lines of the Attr.View and join that with the Anal. View. 😕

        Thanks.

        Kris

        (0) 
      2. Abani Pattanayak

        Performance of Star-Join is similar to that of other join.

        In my opinion, that’s the direction we are heading. In future all views will be done using one interface (i.e. Calc View). HANA will figure out which engine to use (OLAP, JOIN or CALC engine). All HANA live models are built using “Calc” views only.

        (1) 
  1. Justin Molenaur

    Client just upgraded to Rev 74 and I have tried this a number of ways and I cannot get even a very simple star join calc view to return any data at all. This is when the components individually work fine. I have an OSS message open currently to investigate.

    Regards,

    Justin

    (0) 
  2. Martin Mouil Padeti

    This is wonderful blog, as Justin mentioned data preview did not bring any results back either on simple dimension (or) cube w/o star join of calculation view.

    Justin, may you please update the SAP response?

    Thank you,

    Martin

    (0) 
    1. Justin Molenaur

      Martin, apparently there is a difference in how the Analytic Engine and the Calculation View handle Session client of the DB user. When not populated, the Analytic Engine behaves as cross client, but the Calculation Engine interprets that the user is not allowed to see any data. This will be a correction for a future revision.

      The solution would be to either populate a session client on your DB user OR set the default client on the calculation view to cross client. Most environments that I have seen don’t maintain client at the user level, so it will be an issue for them as well.

      Happy HANA.

      Justin

      Detailed response from AGS.

      we have been testing and discussing this

      further internally to determine specifically what is going on.

      The Default Client property of an information view is used to filter

      and see the data that is relevant to a specific client as specified in

      the table columns like, MANDT or CLIENT.

      Prior to SPS 7, only the analytic engine implemented this

      functionality.  As of SPS 7, this client filtering functionality is

      now implemented and enabled for both Analytic and Calculation view.

      (More on the assignment of the default client and the changes for SPS

      07 can be found in Section 7.7 of the SAP Hana Modeling Guide –

      http://help.sap.com/hana/SAP_HANA_Modeling_Guide_en.pdf)

      The Default client can be set to one of:

         – “Session Client”

         – “Cross Client”

         – a three digit positive numeric value i.e. ‘100’

      When set to ‘Cross Client’ the view will display the data essentially

      with no filtering applied to the MANDT or CLIENT field.

      When set to a hard coded three digit positive numeric value, the data

      will be filtered to display only those records where the value of

      MANDT or CLIENT is equal to the client number.

      The ‘Session Client’ works similarly to the hardcoding of the client

      number, except that the client number is drawn from the authenticated

      users database session.

      This session client value is configurable at the user level.  If you

      view a user in the HANA Studio, one of the properties on the user is

      ‘Session Client’.  If that is set, that value will be used

      as the filter value for views as the client value.

      I wanted to provide this bit of detail and background to explain the

      client concept.  You may already have been aware of this but if not I

      wanted to cover that piece.

      What we have found however is a differentiation between how the

      analytic view engine and the calculation view engine handle a specific

      situation with this default client setting.

      The specific case is where the default client is set to a value of

      ‘session client’ but no ‘session client’ is specified on the user.

      In this case the analytic view engine behaves as if ‘cross client’

      were selected and no filtering is done on the data.

      The calculation view engine however, defaults to the behaviour of

      applying a filter resulting in no data being displayed.

      This is the behaviour of the system as of SPS 07 and is clearly an

      inconsistency in how the different engines have opted to handle this

      specific case.

      Once you have recieved my update and explanation please let me know if

      you have any questions and also please send the incident back to me as

      I will be submitting this to our Development teams

      as this inconsistentcy in how the two engines are handling this

      scenario needs to be reviewed and corrected.

      (0) 
      1. Chandra Sekhar

        Justin, That’s an amazing explanation in detail. Changing default client to cross client really helped us also to come out of the issue and see the data preview. Hope this inconsistency behavior of different engines will be fixed soon.

        Regards,

        Chandra.

        (0) 
    1. Justin Molenaur

      No problem. This is actually the case with any NEW calc view that you may develop in rev 70+. Unless you are maintaining a session client in the DB user directly, all calc views must have “Cross Client” as the default client in order to return data.

      At the customer I am working now, all of the EXISTING calc views that were present in 69.01 were converted to use Cross Client during the upgrade.

      Regards.

      Justin

      (0) 
  3. Ankita Bhagat

    I have implemented same scenario with Calculation View as Cube. In this I haven’t created any analytic view or attribute view or Calculation view as dimension.

    I got the same result. Now can anyone please explain what the draw backs by using this method?

    (1) 
    1. Justin Molenaur

      There isn’t a drawback necessarily, I believe the star join is just another way to model. There are still some outstanding questions (for me) about the ability of the star join to perform join pruning like a graphical calc view does.

      Regards,

      Justin

      (0) 
      1. sidharth mishra

        Hi Justin,

        Yes you are right that STAR join is actually no value addition and it is just another way of doing things.

        But i did not understand your comment about join pruning.

        What do you mean by join pruning?

        Best regards,

        Sidharth Mishra

        (0) 
        1. Justin Molenaur

          Join pruning meaning that when a join doesn’t need to be executed to satisfy a query, then it won’t be. Much like a left outer join in an analytic view.

          I have some initial results that show even with all left outer joins in the star join, ALL of them were being fired even if I didn’t have a query that required them. Need more testing to validate.

          Regards,

          Justin

          (0) 
  4. Viren P Devi

    Hello All,

    Advantage I see here is we do not have to create Attribute and Analytic view separately and include them in Calc view.

    But we still need to create dimension level views. So in terms of efforts probably it is same?

    I understand that based on category Dimension/cube, system determines which engine to use. So we may not have performance issues.

    Overall most important thing I can think of is one step closer to simplifying designing

    process.

    Correct if I am wrong please.

    Regards,

    Viren

    (0) 
  5. Ricardo Sada

    Hello everyone,

    I don’t quite understand how the fact join part works. Why would I use an inner join for two fact tables that might have the same or different dimensionality (as in this case). Joining by emp_id doesn’t exactly make sense.

    Can anyone explain?

    (0) 
    1. Lars Breddemann

      Maybe a bit late but here’s my shot at it 🙂

      The point of the STAR JOIN is to allow for key figures/measures to originate from different tables.

      In the example provided in this blog post, the two key figures SALARY and BONUS are stored in two separate tables.

      While the classic analytic view star join allows you to use a join result as it’s data foundation (which really is the fact table) but it only supports key figures to come from one of the tables.

      Basically, this means that it merely allows you to increase the number of dimensions of your fact table.

      The STAR JOIN feature is more flexible here as key figures from multiple tables are supported. With that you can actually stitch together a fact table/data foundation with both dimensions and key figures originating in multiple tables.

      This might not be the best performing option in all cases, but it certainly is a way to make data transformation and duplication by storing data in a cube-like format just for reporting purposes superfluous.

      – Lars

      (0) 
  6. Justin Molenaur

    Has anyone actually seen success using these Star Joins? I am working with a client where SAP resources suggested to convert all the analytic views (which perform well already) into Star Joins in order to prepare for data tiering features coming down the road.

    Using the “Mass Copy” tool, you can convert analytic views automatically, in a simple example, here are both.

    AN view – very simple

    Converted Star Join

    In the Plan Viz for the Star Join, I can see that the entire table is being processed, likely due to the joins. Maybe an aggregation node in the middle would help, but this seems like very poor performance from initial investigation.

    AN View Viz Plan – simple query, no joins requested – 80ms

    Star Join Viz Plan – similar query, no joins requested – 12 seconds

    Additional, there is the option of “Optimize Join Column”, when set will avoid joins when the columns are not requested. However, as soon as I request a column that involved a join, performance plummets again – so I’m not sure this is a good option either.

    Regards,

    Justin

    (0) 
    1. Benedict Venmani Felix

      hi Justin,

      I tried using them once for one of my models, sometime ago, but I wasn’t happy at all about the performance either. I do not have the statistics for it now. But I observed something similar on how all columns were being processed.

      Benedict

      (0) 
    2. Abani Pattanayak

      Hi Justin,

      As of today i.e. HANA SP09 (Rev 96), I’d not recommend converting Analytic Views to Star-join calculation view. You’ll get better performance with Analytic View in SP09 in general.

      However, in future there will be no Analytic View (only calculation view).. So if there is NO compelling reason (i.e. if you are using SDA or DT), you should hold your migration.

      Regards

      Abani

      (1) 
      1. Justin Molenaur

        Yes, that’s what I observe as well regarding performance.

        As stated above, the client is looking at DT so therein lies the issue.

        Regards,

        Justin

        (0) 
        1. Justin Molenaur

          Just wanted to follow back up with some interesting results. I took some benchmarks between an analytic view, star join and just base tables. I then exported the same exact data set for comparison on a SPS09 scale up instance. All exact same setup with regard to view design, and the same queries.

          So what we can see is that there are major improvements to star join execution in SPS09 vs. SPS08. In the simple cases, the star join actually outperformed. But when additional joins start to get added (behavior starts at 2 joins +), there seems to be some type of “fixed” time cost – you can see that there is a constant 410ms difference for the last few scenarios tested in SPS09, must be slightly higher overhead to handle multiple joins.

          It seems fairly reasonable ion terms of performance and gives a more flexible option for designing re-usable objects. Much like the analytic view/attribute concept, now we can reliably use all calc views instead – which is the forward looking vision. I was just interested in proving out the viability with regard to performance.

          I need to perform this same test on a revision 96 instance to see if things improve from rev 90, but thought this was valuable to share back.

          Happy HANA!

          Justin

          (0) 
          1. Benedict Venmani Felix

            5 Stars Justin!!!

            Your discussions always interests me, because, unlike some of us(including me 😉 ) who leave discussions in the middle, you always come back with your findings, substantiated with facts.

            Benedict

            (0) 
  7. Jonathan Haun

    I know this is an older posting, but I am curious to know if in the example, both FACT tables have to have the same granularity or carnality? For example, I am assuming this would not work if the join between fact tables had a 1:n (one to many) carnality. Think of Header and Detail tables where I need to define measure again both. Would this not create a SQL FAN Trap?

    Assuming that it works, I see an advantage in the Dimension based calculation views with this methodology. For example, you can project, join, union and aggregate the data before sending it to the final project node in a Dimensional Calculation view.. This can help in situations where more complex manipulations are needed to generate the “dimensional” components of the multi dimensional model.

    (0) 
    1. Lars Breddemann

      I’d say that this construct would violate the star-/snow-flake data model.

      For that to work it’s key that each row in the fact table represents one specific set of facts that are dependent only on the dimension values.

      So, technically speaking, the dimensions should form a candidate key (even though it might not be implemented).

      The ‘fan trap’ (rather a BI term, that I just learned here simple example for SQL traps [chasm / fan trap]) occurs only when you accept that the same fact is repeated due to the n:m join.

      In order to avoid confusion about the semantics, I would probably try to avoid such situations and rather build one cube more instead of trying to cram every single key figure into the same information model.

      (0) 
  8. Prasana Ravichandran

    Hi Experts,

    I have a calculation view with Star join included (inventory details as fact table , joined with Site table as dimension).

    When i query a Select count(*) from <view>        –> i’m getting  300k+  as count.,

    But where as when i use this star-join calc view in any other view i’m able to get a count/rows of 262k+ only.

    Any idea whats happening and where am i missing something !!

    Thanks and Regards,
    Prasana Ravichandran

    (0) 

Leave a Reply