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.
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
Attribute view, Analytical views or base table cannot be used in STAR
Performance is not tested
3NF design is possible with star Join
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
B.4) STAR JOIN
C. Activate and Review the data.
Thanks for reading my blog. Please provide comments and feedback.