This content has been marked as final. Show 12 replies
Can you please explain how you have set the MTD and QTD fields in the BMM? Also, please give the structure of your time dim and the fields used for each chronological key.
I think the issue is with the way you have set up the hierarchy. If you can let me know how you have set it up, I would be able to help you better.
Thanks for ur reply
my hierarchy is
year------------year filed values---2011,2012,2013...
quarter-------quarter filed values---q1-2011,q2-2011,q3-2011,q4-2011,....
month -------month values------Jan-11,feb-11,mar-11,apr-11,jun-11,....
i set chronological key for all levels year ,quarter,month
i created qtd----todate(0
functions are correct
ur correct that hierarchy has problem
i sort the monthname field by startdate
i dont know why the values takes like ascii format(a,b,c,d, ...)this format for both quarter and monthname
please can you suggest me in this
It sounds like you are on the right track. The lack of some fact records for specific dates should not matter. The Time Series Functions in the RPD are totally reliant on the time dimension being setup correctly, so I would first suggest checking the time dimension (hierarchy) setup. A common problem is with level-based keys. Check that keys have been defined correctly at each hierarchy level (except the Grand Total level); make sure that you have a sequential chronological key* defined at each dimension level, primary and drill down keys have been defined at each level (as required) and the grand total level has been correctly flagged. Don't forget, the dimension needs to be flagged as a time dimension too! There is plenty of guidance on the web about setting up and using time dimensions if you need more detail.
* If you think about this laterally, the time series functions need to know how to work out what a measure value was x periods ago. Periods could be years, months, weeks, quarters; as defined by the level variable in the time series function. So, based on a prompted period, say Mar-2012, the BI server does not have any inbuilt calendar logic that knows that two periods ago it was Jan-2012. Our calendar could be defined using some strange period names e.g JN10-11, FB10-11, MR11-12 etc. We need to tell the BI server how these periods are sequentially (or chronalogically) related. This is where the chronological keys come in which we define in the dimension.
Also, ensure that the aggregation method of the base measure is Sum.
Check the syntax you are using for the time series function:-
AGO(measure, time dimension level, number of period(s) ago) e.g. AGO(Core."Fact - Project Cost".Cost, Core."Date - Fiscal Calendar"."Fiscal Period", 1)
Do you get any RPD consistency check errors? Have you created a logical join between the fact and the dimension tables in the business model?
Finally, have you looked at the physical SQL being generated to get some pointers? You can do this by ensuring that the log level variable is set appropriately to display physical SQL in the query logs (e.g. level 2).
Plese refer the below link for more details.
Hope this help's
Thanks for your valueable information
im using logical join between fact and times table in bmm layer
is this any problem
i have another suspect
first time i created hierarchy year-quarter-month
later i want to change the hierarchy bur it is not supposed to change the hierarchy
that means if i want to change the key column in any level then it give consistency error
why it happens like this
I guess your in confusion mode.
Please cross check your time dim hierarchy and chronological key and level.
I can suggest you please take rpd in your local system then create new dim hierarchy and define the keys check consistency error. This way you will find the issue easily.
Hope this help's,
thanks for ur reply
thats ok,i didnt get consistency check error
but i didnt get monthnames orderformat
it takes like Aug-11,Apr-11,Dec-11,...like this
my month values are taken as ascii format(a,b,c,d...)
for exm--for 1st quarter it tales feb as first month , jan as next ,march as next like this
mainly todate() and ago functions have problem for this
functions work correctly
but data is not correct it is based on ascii format
please help me in this
Edited by: ARYABRAHMA on Mar 20, 2013 4:10 AM
my data is like this
quarter month amount qtd amount qago
quarter Period name Net amount QTD
Q1-2011 JAN-11 536100.00 1300578.87
FEB-11 764478.87 764478.87
MAR-11 782214.21 2082793.08
Q2-2011 APR-11 815363.61 815363.61
MAY-11 805367.12 2725282.52
JUN-11 1104551.79 1919915.40
Q3-2011 JUL-11 835386.90 1674954.94
AUG-11 839568.04 839568.04
SEP-11 861497.21 2536452.15
Q4-2011 OCT-11 66542.59 2330157.05
NOV-11 1382129.01 2263614.46
DEC-11 881485.45 881485.45
Q1-2012 JAN-12 94633.15 4760049.24 2082793.08
FEB-12 4665416.09 4665416.09 2082793.08
MAR-12 103171.38 4863220.62 2082793.08
Q2-2012 APR-12 86618.34 86618.34 2725282.52
MAY-12 85079.62 243996.08 2725282.52
JUN-12 72298.12 158916.46 2725282.52
Q3-2012 JUL-12 59328.51 111524.33 2536452.15
AUG-12 52195.82 52195.82 2536452.15
SEP-12 560319.49 671843.82 2536452.15
Q4-2012 OCT-12 1491105.39 21534004.23 2330157.05
NOV-12 19780107.55 20042898.84 2330157.05
DEC-12 262791.29 262791.29 2330157.05
Q1-2013 JAN-13 257640.02 490347.12 4863220.62
Rows 1 - 25
can see in the qtd and qago data
Edited by: ARYABRAHMA on Mar 20, 2013 4:29 AM
I understood, Do one thing in your Time Dim tables having one time key column value like(20110121). If you having this column apply chronological key in this column that to lowest level and check it.
Note: Don't define chronological key each and every level just define only lowest level. Ex(Date column (or) Time key column).
Please do the above steps and update the post.
Im using start date for lowest level that is in timestamp format this level is now newly added
then i get the below error
Business Model REVPROOBIEE:
[nQSError: 15019] Table Periods is functionally dependent upon level Month, but a more detailed child level has associated columns from that same table or a more detailed table.
[nQSError: 15001] Could not load navigation space for subject area REVPROOBIEE.