How to avoid Case statement logic in Fact table - for Dates

[cognosun]

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.

[Lynn]

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.