Help: Date Dimension Table

[Former Member]

Hi everyone,

I am currently working on WEBI. I am totally new to to it as I started working on it about few weeks ago.

The universe which I am working on does not have the date dimension table. With operational challenges we cannot crete one as it requires certain cost and have few challenges. The transactions are placed in a table with a date. but there is no seperate date dimension table. So now when we have to see month wise trend, What i Do is get the complete transactional data and then create a variable which further divides the data into months etc

The challenge here is that due to the data size the query takes hours. Is there any other solution where we can get month wise data in some quicker way.

I need your help to resolve this issue.As i am totally new to this tool. I will be greateful if anyone can share steps etc. Thanks!

[Former Member · Accepted solution]

You will not need  a date dimension always to show a month wise trend neither will it significantly improve your query performance as you can have a month object in your universe using a to_char(table.date,'MM/YYYY') and use this object for your trending in the webi report..

How much data size are we talking of here.. you can also create a aggregate table just with monthly data and use aggregate awareness function in universe in the database which can significantly reduce run time...

Also please make sure indexes are built on the column you are filtering on and if there is any joins which are there with high volume tables use the key or indexed column and defined index awareness on them..

[Former Member · Accepted solution]

Thanks Alot Durgamadhad for the help.

I am working on a Sales Department project at present. Let me give you a brief overview of DB Strucutre:

in first Table i.e. Invoices there are sales invoices along with date of sale and customer information. whereas the detail of each invoice is stored in another table i.e. Invoice details. Invoice ID is common in both (i.e. is the Key)

Currently for this year I have around 20 million rows in details table. So what i am doing is get the date wise table through a query. Then I see the month wise trend by help of certain variables.

The query takes hours to get the data. Indexing etc is already there in DB.

I tried what you told me but I think I may be doing it wrong or missing something out ( as I am totally new to this tool ) . I will be very grateful if you can explain it bit more or suggest something. Thankx alot

[Former Member · Accepted solution]

In your scenario as you are having a 20 million fact table for a year and you are always looking for a monthly aggregated data in your report ; so I will suggest to create a MV (on indexed view is DB is SQL server) and store the data aggregated in a monthly aggregation level. This will give you significant performance improvement. Also there might be some other ways to tune the query that might give you marginal result  but its impossible to suggest them without looking into the query and explain plan of the query.