"Reusable cube" for dimensions

[dougnewton]

in a typical corporate data warehouse scenario, we'd have a wide range of facts covering different areas of the business.  They in turn would likely be set up as different Elasticubes/models that have a small number of facts in each.

However, it would be likely that many of these cubes would have dimensions in common among them.  Think of a dimension for Customer, Employee, Location, Product, Calendar, etc.  Those would likely be on lots of cubes.

What I'd like to do is clump all of my shared dimensions into their own cube, that isn't referenced directly by any dashboards.  Rather, other cubes would pull in those dimensions and join them into their local fact tables.

That way, I can only build these dimensions once, schedule them to all occur early in the loading cycle, and in general promote reuse,.  With the same dimension essentially "copied" onto multiple cubes, there's a tendency for each instance of that dimension to get tweaked over time and you have redundant or inconsistent logic.

Is this possible to do in Sisense?  Or more accurately - is it an intentional design feature that is supported and is known to work properly?  🙂  

Thanks!

[rapidbisupport · Accepted solution]

Certainly! To implement shared dimensions using the EC2EC connector in Sisense:

1. Create a Central Dimension Cube: First, build an ElastiCube (let's call it 'CentralDimensionCube') that houses all your shared dimensions such as Customer, Employee, Location, etc.
2. Use EC2EC Connector in Other Cubes: In your specific fact-focused cubes (for example, 'SalesCube'), use the EC2EC connector. This connector allows you to reference the shared dimensions from 'CentralDimensionCube'.
3. Link Dimensions to Fact Tables: Within these specific cubes (like 'SalesCube'), link the dimensions pulled from 'CentralDimensionCube' to your local fact tables. This creates a connection where your fact data in 'SalesCube' can utilize the dimensions defined in 'CentralDimensionCube'.
4. Schedule Builds Appropriately: Ensure that 'CentralDimensionCube' is built or refreshed before your other cubes. This ensures that the most updated dimension data is used across your cubes.

By following these steps, you’ll efficiently reuse dimensions across multiple cubes in Sisense, maintaining consistency and reducing redundancy. 

We'd like to hear how you go!

Attachment: Screenshot of the EC2EC connector on the "Choose Connection" page.

Derek N

[email protected]

RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons

[dougnewton]

Thank you!  I saw that it was possible to do this, I just didn't know if it was a good idea or not.   🙂   Is it an efficient use of resources, will Sisense tech support think it's OK, and so on.

[dougnewton]

Been trying this out the last week.  It works perfectly for small or midsized tables (i.e. most of the dimensions).

I did try creating a "reusable fact" cube with the base transaction-level detail, and then using that in a variety of other cubes where the data was aggregated.  About 200M rows in that base fact cube.  Sisense was considerably less happy about this.  Often times the builds of the dependent (aggregate) cubes would hang.  Maybe this is a configuration problem on my cloud Sisense server, but for now, I think I'll stick to the smaller tables in my shared cubes.