MSTR - Date manipulation

[neilgalyans]

I hope someone can help me. Briefly, my company is new to MSTR, and I am setting up the database BI project. I have looked thru other posts and tried some things to solve my problem, but I am stuck:

Our database is set up where each line (say a sales database table) has a Year column, Period column, Week column, Day of week column, and DATE column. I have set up my time attributes to mimic this, including an attribute called date : which has an ID expression of Year,Period,Week and Day of week. I also set up the DESC form as the DATE column.

When I create a report, and specifiy the individual Year, Period, Week, Day of week the report runs very fast. (I am using report filters to query the user for the date info needed) HOWEVER when I query on the DATE (which is in the format MM/DD/YYYY) the report slows down - and sometimes times out.

I do not know how to rectify this situation - we are using an Oracle Database, I have tried the trunc function to no avail, perhaps I am using it wrong.

Any help or suggestions would be appreciated.

THANKS

 

[Z3]

What's your SQL look like? You probably should define many attributes (not just one attribute with many forms) for the Time dimension. You may want to make a Year, a Period, and a Week attribute. Anyway, take a look at the example project and get some ideas.

 

[neilgalyans]

Thanks - I have set up my time heirarchy Year, Period, Week and Day...

The SQL looks like:

select a12.PRD_LVL_CHILD PRD_LVL_CHILD,
max(a13.PRD_NAME_FULL) PRD_NAME_FULL,
a11.SLS_YEAR CALCDY,
a11.SLS_PERIOD CALCPR,
a11.SLS_WEEK CALCWP,
a11.SLS_DAY CALCDW,
max(a11.SLS_DATE) CALCUR,
sum(a11.SLS_AMOUNT) WJXBFS1
from SLSTH1EE a11,
PRDMSTEE a12,
CLASS a13
where a11.SLS_PRD_CHILD = a12.PRD_LVL_CHILD and
a12.PRD_LVL_CHILD = a13.PRD_LVL_CHILD
and (a11.SLS_ORG_CHILD = 0
and a11.SLS_DATE = To_Date('26-Oct-03'))
group by a12.PRD_LVL_CHILD,
a11.SLS_YEAR,
a11.SLS_PERIOD,
a11.SLS_WEEK,
a11.SLS_DAY


Attribute Day is as follows:

ID
Year Number
Period Number
Week Number
Day Number

Date (Desc) Datetime

I hope this helps

THANKS AGAIN

 

[JaC74]

Oracle should have an "Explain" function where it steps out how the SQL will be run. Do an explain on the SQL where you specified the individual Year, Period, Week, Day of week and the do an explain of the SQL where you just qualify on the date. Most likely, you are not getting indexed access to the fact table when you are going by the Date alone. Any reason why the ID column for the Date is set to Year, Period, Week, Day instead of having it's own Date ID?

 

[neilgalyans]

Thanks - that was a help.

I have narrowed the problem down further...

I have a sales table that is partitioned by year and month combinations. The query is looking at the entire table instead of hitting the partition directly.

Given the attribute structure above - how do I tell microstrategy about the partition?

THANKS

 

[chael]

Check out TN4100-7X0-0098 as well as the Partion tables that exist in the MicroStrategy Tutorial project. You need to bring the partioned tables into MicroStrategy via the Warehouse catalog.

Chael

 

[neilgalyans]

GREAT -- this has been a great help. I was able to create the Metadata partitions for my sales tables. No however, I am having a hard time getting specific data from the table.. we store weekly subtotals in our database as follows:

Year = 2003
Period = 10
Week = 4
Day = 0

My user will input a date and then I will provide sales for the day - and the weekly number from day = 0 . My problem is that having created the partitions - I created a Weekly Sales Metric - with a conditional filter that says Day = 0 but - I am now returning no data:

from CALDTLEE a21
where (a21.CALCUR = To_Date('2003-10-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and a21.CALCDW = 0
and ((a21.CALCYR = 2003
and a21.CALCPR in (9, 1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12))

The Calendar Day table Caldtlee does not show the Day = 0 line, only the sales table..

Can anyone advise.

THANKS this board is great!