Hi,

Starting with new scorcard, when we load time dimension choosing the option of Table Type "Separate Table" we are getting error when we save it even before loading. In screenshot-1, it gives me a warning(not sure why?) and in screenshot-2, it gives error while saving it. In other screenshots, I dropped quarter and week so that month is lowest dimension but still same error. Not sure whats wrong with our data.(I am attaching the .txt and .mas file)

When we load the embedded time dimension of PMF, I am not sure whether Year, Quarter and Month which gets loaded are alphanumeric or date fields . Because if we use embedded time dimension, our fact data is not joining to dim as it gives error "Exec error when loading measures". Wondering how to confirm whats wrong on data side?

Is it a good option to always use embedded time dimension or prefer customer time dimension (which they welcome in most cases based on data structure)

Thanks for help.

Regards
Abhinav

This message has been edited. Last edited by: <ABHINAV BALI>,

SCREENSHOTS.zip (126 Kb, 3 downloads)

You can set up a custom Time Dimension. If you do so the data source you're using to build the Time Dimension must have all the fields in all the proper formats.

Have you referred to the documentation on how to do this? It is covered in pages 285 - 290 of the PMF documentation - "Designing a Custom Time Dimension Load".

Thank you for attaching the Time Dimension source files as this pointed immediately to the sources of the problem you are having:

1. The files you set up do not have the proper fields in the proper RDBMS formats. There is a predetermined format you have to use for custom Time.

2. It doesn't contain the linkage keys for Time to Measures (you have to link to keys with custom Time since there doesn't yet exist the juju needed for PMF to magically figure out arbitrary custom date math from sample data).

3. It doesn't tell PMF how to map prior year to current year. Weekly calendars are by design irregular (52 or 52 weeks per year, and if you're going by calendar quarters and months which can contain irregular numbers of weeks, you have to tell the system where those orphan weeks link up for the prior year).

The sample data does seem to contain gapless Time data - that's a good start. It means you and the customer have specified some of what is needed to build a Time Dimension. Now you just have to do it the recommended way and you're there.

Give the doc a read.

This message has been edited. Last edited by: Bob Jude Ferrante,

By the way, for Time Dimensions, as with all Dimensions, all Time is embedded. So regardless of whether you use Time AutoGen (which gives you a regular Gregorian Y-Q-M calendar) or custom time (which lets you do fancier stuff), PMF always creates a CALENDARS table or loads a CALENDARS table from your provided Time specification.

PMF's CALENDARS table contains the dates corresponding to the time that is specified for all of the Time you want PMF to care about.

That means all Time Dimensions are ultimately specific and defined master data. The AutoGen time is just easier and quicker to generate, because it's based on common rules and assumptions we all go by every day.

Hi Bob,

Thanks. I was able to load time dimension successfully from separate table making sure that it has data in same format/type as mentioned in manual.

1)But wondering why date mapping option during "Field Map" gets hidden when we load time dimension from separate table.

2) Looking at timesource_irreg_1.csv in pmfdemo folder location, I am not sure why TIME_KEY is number 1, 2, 3, 4. Why not combination of Year,Quarter and Month (20070101, which will be unique on every row).

3) While loading measures when we move to dimension tab, it ask to join from key field for "Time". Now since we are aggregating data at lowest level which is month, which column in Time Dimension should we map. Month? or TIME_KEY.I am mapping Month to OrderDate(in .csv attached) but "exec error while loading new measure" when I try to load it. I have checked that my data types are same for Month, ProductId and StoreId.

Attached is my Fact table(.csv).

Thanks
Abhinav

fact.csv (594 Bytes, 3 downloads)

Answers...


1) Thought we answered that one in previous posting... but... because a custom Time Dimension can be any arbitrary chopping up of Time, it's not possible to use the date conversion to Time keys. You have to link direct to Time Dimension.

2) Time_key is any arbitrary key. It *can* be set to larger key values such as 20100101 if you desire but it doesn't have to be.

3) You do not *have* to link all loaded Measures to the lowest level if all Dimension levels are keyed, but if keys are only present for the lowest level you'd have to link to that level. If you're using WEEK level Dimension lowest level would be WEEK. If you're using a MONTH based system lowest level would be MONTH.

Hope these answers help
thanks