Skip to Content
Author's profile photo Former Member


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.


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.




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

  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.


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


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


B) Create a New Star Join Calculation View

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


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


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


B.3 ) Join Two Fact Tables




C. Activate and Review the data.


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

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Very useful new feature. Thanks for sharing, Anand!

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Kris Claes
      Kris Claes


      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. 😕



      Author's profile photo Abani Pattanayak
      Abani Pattanayak

      Why are you joining an Attribute View with Analytic view in a Calculation view. Should you not push that join to the Analytic View itself.

      Author's profile photo Abani Pattanayak
      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.

      Author's profile photo Former Member
      Former Member

      Hi There,

      Thanks for the timely post, at least for me. I have encore time wondered about the transition, but somehow did not dare, because the information I came across very controversial.

      But today, after reading your article I’m very confident to take up any challenges.

      I want to use the HANA Predictive Analysis Library in our SAP Cloud Platform and tried to check the installation following the official help site.

      The following statements do return the expected result:




      However, in the catalog the APL procedures don't show up in the _SYS_AFL schema (even though the Functions exist):


      When trying to call a PAL procedure (using the example code from the official help site), I get the error:

      Could not execute 'CALL _SYS_AFL.PAL_KMEANS(PAL_4_COLUMN_DATA_TBL, "#PAL_PARAMETER_TBL", ?, ?, ?, ?, ?)' in 25 ms 398 µs . SAP DBTech JDBC: [328]: invalid name of function or procedure: PAL_KMEANS: line 1 col 15 (at pos 14)

      It enables technical users to manage the SAP HANA database, to create and manage user authorizations, to create new, or modify existing models of data etc.,

      Can someone help me out with this?

      Anyways great write up, your efforts are much appreciated.

      Many Thanks,


      Author's profile photo Justin Molenaur
      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.



      Author's profile photo Martin Mouil Padeti
      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,


      Author's profile photo Justin Molenaur
      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.


      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 -

      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.

      Author's profile photo Chandra Sekhar
      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.



      Author's profile photo Martin Mouil Padeti
      Martin Mouil Padeti

      Thanks Justin for prompt reply and its really worked.

      Author's profile photo Justin Molenaur
      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.



      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Justin Molenaur
      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.



      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Justin Molenaur
      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.



      Author's profile photo Viren P Devi
      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


      Correct if I am wrong please.



      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Lars Breddemann
      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

      Author's profile photo prabhith prabhakaran
      prabhith prabhakaran

      Thanks for sharing.. Good One.



      Author's profile photo Justin Molenaur
      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.



      Author's profile photo Benedict Venmani Felix
      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.


      Author's profile photo Abani Pattanayak
      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.



      Author's profile photo Justin Molenaur
      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.



      Author's profile photo Justin Molenaur
      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!


      Author's profile photo Benedict Venmani Felix
      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.


      Author's profile photo Tejash Bavishi
      Tejash Bavishi

      Hi Justin,

      This was good stuff on the comparison... just curious to know if you did this comparison in the recent releases also...we are on SP 12 and it still seems that Analytical views are performing better than Star Join Calc View. We are using HANA views in BO Explorer and can see the difference. It would be great if you share you results if get chance.




      Author's profile photo jitendar kumar
      jitendar kumar

      hana SPS08 database installation failed on redhat 6.4 64 bits

      Hi All. above is my hana installtion error link..

      please help me.....

      Author's profile photo Rama Shankar
      Rama Shankar

      Good Blog - thanks 🙂

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Lars Breddemann
      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.

      Author's profile photo Prasana Ravichandran
      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

      Author's profile photo Nishant Gupta
      Nishant Gupta

      very helpful blog!!

      Author's profile photo Disha Sugandhi
      Disha Sugandhi


      Sorry commenting on an old post.

      I am facing a performance issue in Star join HANA SPS12 views. When I checked the plan viz, I noticed some logics getting executed which have no connection to the attributes called in SQL.

      So my question is that is it possible that calculated attribute created at the star join node, will it always execute (but not show in output unless called), whether called or not.

      Can anyone please help.Thank you