nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

HR Analytics: Monthly vs Daily

Judith Ullman

Assuming nightly incremental loads, we are trying to understand what items we will see change on a daily basis and what will be on a monthly basis. In HR Analytics, Subject Area Workforce Deployment, the main fact table is a monthly snapshot. Nevertheless, some information can be seen on a daily basis. For example, if an analysis shows an employee's name, you can see the name change if it changed in the source system the day before. But is this true for all dimension attributes? Or do some only get updated for the monthly snapshot?

On a higher level, we have found absolutely no information about how the system is supposed to work, except the very few notes in the Configuration Guide. We remain perplexed how such a large, complex, system can be undocumented, and how all the other customers figure out how it works. For example, we spent months - even with Oracle Support help - figuring out how Headcount is counted.

All input appreciated!

Thanks,

Judith Ullman

City University of New York

Find more posts tagged with

Accepted answers

All comments

Thomas Dodds

Most implement via a partner - partners bring the value of understanding the source system(s), the ETL, the BI models and the visualizations -- having established practices and having done it many times. In addition to that they also have customization approaches which are essential when upgrading the OBIA in the future ...

Additionally ... having an understanding of dimensional modeling will help you answer your questions. Employee is a type-2 slowly changing dimension (SCD). It stores the history of changes. Those changes are effective dated. When the SCD key is joined to a fact either of the following can be specified:

AS_IS -- historical fact data as seen with current dimension attributes

join the fact on SCD key AND current_flg = 'Y'

AS-WAS -- historical fact data as seen with the dimensional attribute value it was at the time of the fact

join the fact on SCD key and the fact date between the SCD effective start and end dates

Knowing the above you can see how a name change can get 'lost' or not based on how the BI model is set up. Also, you can see that on a monthly fact with an AS-WAS setup, you'd get the dimensional attributes as they were at the time of the snapshot.

EX: Change a name a day after the monthly snapshot; AS-IS see the new name as soon as the dimension is reloaded; AS-WAS wait until the next snapshot is taken as the new name was not effective yet.

Lastly, fact stars exist to answer specific questions about the business. The monthly workforce event snapshot table is designed to answer a specific set of questions about the workforce and to supply information to the pre-built dashboards/reports. Seems as though you have additional/varied requirements to the pre-built content; if so, then you are into the customization side of it and I URGE you to seek the assistance of a partner -- especially one that can demonstrate a solid approach to customization in the face of upgrades.

If you want to just browse the employee records independent of the monthly snapshot fact -- there is many logical ways to build a logical star to achieve that.

Judith Ullman

That is very well for implementation partners, but Oracle sold us the system as working OOTB. Even if that's false (and some of us knew it was) we do not have the option to get a such a partner. In any case, the system should still have some basic documentation. To deliver, for example, Headcount, with only reverse engineering to determine how it works seems pretty specious to me.

Moreover, we have run into a ridiculous number of bugs (hundreds - verified by Oracle) and other problems. For example, the setup tool that is delivered (BIACM) lets you choose certain dimensions to be Type 1 or 2. But it turns out that they are hardcoded in the ETLs so the settings are ignored (again, verified). With problems like this, it is extremely difficult to know which aspects of the system to trust.

I would be interested to hear about approaches different consultants take, and different customers take.

Thomas Dodds

It does work OOTB ... do you have the Data Model Reference, Product Guides, etc for your version of OBIA? What version do you have?

Judith Ullman

We have all of that, and more. It seems that we have a difference of opinion on what knowledge we should need to implement. As I've said, we've had a tremendous number of bugs. But to me the most telling example remains Headcount in HR Analytics. When we run the ETLs and we bring up the dashboards and we see Headcount, we naturally ask "How is it counting employees?". We expect that answer to be documented in English. We do not expect to have to reverse engineer the ETLs to find out what employees are being filtered out, and why, and which jobs are counted and which are not, and so on.

Thomas Dodds

I'll see if I kept any notes from my past implementations on the changes made to the Headcount logic ...

Judith Ullman

I am paying a lot of attention to what you said about "AS IS" and "AS WAS". However, I do not see that in the RPD. I see the WID of the dimension table linked to the WID of the fact table. So wouldn't the definitive join be in the ETLs that create the WIDs, rather than in the RPD?

Judith Ullman

I believe I've found this in ODI. Still, it shows that studying the code, rather than having information presented in a manual, is the only way to find out how the system is supposed to work.

Thomas Dodds

If the ETL is writing the dimensional row WID into the fact, then the AS-WAS is being strictly enforced and it's very difficult to undo once you are under way (need a warehouse reset) ... check to see if the ETL is writing the SCD WID or not.

Ex.

Dimension

ROW_WID SCD_WID EFF_START EFF_END CURRENT

302 123 12/2/2008 1/1/2017 N

....

1001 123 1/1/2017 NULL Y

In the above the SCD dimension record got an update at the start of the year ... see if your ETL is writing the 302,1001 WIDS or the 123 WID ...