We have a fact table in which we need to display quarters, ex:
Date Qtr&Year
---
Jan 2012 Q1 2012
April 2012 Q2 2012
--------------------
We wrote a case statement ( in FM) and it's working fine, giving quarter numbers. But the problem is ( you can guess) it's a fact table !!! It started giving performance issues as the data started growing.
Can any one help me how to handle this, and there is no date dimension in our schema.
Quote from: cognosun on 13 Nov 2015 07:46:32 AM
We have a fact table in which we need to display quarters, ex:
Date Qtr&Year
---
Jan 2012 Q1 2012
April 2012 Q2 2012
--------------------
We wrote a case statement ( in FM) and it's working fine, giving quarter numbers. But the problem is ( you can guess) it's a fact table !!! It started giving performance issues as the data started growing.
Can any one help me how to handle this, and there is no date dimension in our schema.
The obvious advice is that you should create a date dimension. Sorry but that is the tried-and-true solution.
The only other suggestion I can offer is to replace the case statement with a database function that returns the quarter number. You didn't mention what your database is, but DB2 has a quarter function. I believe MS SQL has a quarter option within the datepart function. Not sure offhand about other databases. Of course it would be a simpler expression compared to a case statement but that doesn't necessarily mean it will perform better. Could be worth a try but really you should just bite the bullet and get yourself a shiny new date dimension.