I know its been a few months since this post was really active, but I thought seeing I had just built a series of Oracle Financials universe that I would put in my 2 cents worth.
Backgroud first. The reason that I created the universes was because we were retiring Oracle Discoverer which had End User Layers mapped over each functional area. GL, AP, FA, SOF, HR
I took a design approach of my customer who said they wanted to see all objects in one universe, but instead of building one universe, I built every one of these individually then created a Master Linked universe containing all the sub-ordinates. The Master universe then contains all the joins between the sub-ordinates and the contexts required and any generic dimensions like dates.
The universe has now been live for a couple of weeks and touch wood, there have been not issues from the customer perspective. I have one on a technical side, but I have put that in a different post and hope someone maybe able to answer this for me. You’ll find that post here
I can see advantages to that approach. Once you have several modules in the universe, doing integrity checks becomes problematic. This way, the users get all of the objects they want, but the universes stay a more manageable size.
Arjan, did you implement any type of row level security? I have a client who is interested in doing so using the set up information in Oracle EBS and I have been struggling on how to do so.
Not in Oracle Financials, but I did do one in an HR universe (with an Oracle database) that I built last year and I have to say that this one and probably still is one of my best pieces of work ever
Basically we have a table based on position numbers and the positions that report to that position, be directly or indirectly and whereever you fall in that food chain will allow you to see yourself and anybody that report to you either directly or indirectly. We then also created an override table that is used for those that require more access then their default. eg the PA of the CEO has no reports direct or indirect and therefore in the normal world would only be able to see there own data, but as the PA to the CEO, they need to be able to run the reports for the CEO, cause lets face it, how many CEO’s do you know that run their own daily reports
Now bearing in mind the Oracle EBS contains the CRM, my guess is that it will also have some sort of table that contains the Org structure of the organisation and if so, then my solution above will work perfectly for you.
The only painful parts about the process are:
a. finding the correct entry point to add the security. There could be multiples.
b. having to add the security table to every dimension that you want to restrict.
If this is the sort of thing your after, let me know and I will elaborate more.
I think there are going to be lots of painful parts of the process. As you said, there are multiple places where the security could be in Oracle and I have to work with the customer to determine just where I need to look for the data. I was thinking I would have to create a some groups in supervisor, one for each segment they want to restrict. A user who can look at data for a whole business unit would go in the Business Unit group, where as a user restricted to a department would go in the Department group. Then I have to see how the performance is impacted. It is going to be a challenge.
Yes, that is right, it will be painful, if you are making Supervisor/CMC groups, then you may find you actually need to set up many more groups than you think resulting in a lot of maintenance worries for the system administrator.
Basically the security that I setup does that by default, every person that I have in the org structure is in a Directorate/Branch/Cost Centre/Section & Subsection hierarchy, with the CEO sitting across the lot.
If the person is a Cost Centre manager, then all they can see from the data is their Cost Centres information being themselves and all staff that are in that same Cost Centre even though they are in different teams.
But if for example, you have a person that is merely a Purchasing Officer, they are considered to be at the bottom of the chain. They can run a report and see only their information.
Based on what you have said, I think my model will work for what you are trying to achieve. But you must have a table with Oracle that contains the Org structure.
Our intention is to have common dimensions on our universes based from our DW once built. How do we ensure the universes are in sync to allow for the merging of data from multiple universes. Are there certain BO architecture principles that you follow that you can share ? We wont always know up from what the next universe is going to be but when the time comes we want to be sure it aligns with what we already have. Appreciate any guidance you can provide on this subject.
I’m not a massive fan of linked universes, however I appreciate that it would provide the type of conformity and ease of maintenance you are after.
This is extremely important and when developing against a dimensional warehouse it has always been on the forefront of my mind.
There are limitations, these need to be carefully considered to see that they will not hinder the answers, to your requirements, although others have had good results by doing it this way…
I guess this is the best approach as we are following this approach. Just kidding.
Truely speaking it would be easier (maintenance wise, as well as debugging wise) if you create separate universes. Like we have 15 universes -
One for Invoice Module
One for Order Module
One for Purchasing Module
One for Account/Allocation Module
Sometimes we get a request wherein we need to merge two data providers to fulfill the requirement, so I think that is less time consuming than creating a single universe and maintaining it throughout the project life and even after that.
That may be true, but the point of the universes isn’t to make your life easy.
My guess is that your company’s users stick to simple reports because that is all the reporting solution you have provided will support. Any complex analysis they are doing by running multiple reports, throwing the data into Excel then slicing and dicing.
To me, I want my power users to have all the options they can handle in BusinessObjects, because if they can’t get the answer they want with BusinessObjects then they will use Excel.
I have to agree with Dennis. Users can be a fickel bunch and not providing them all the options can limit what they create.
Aniketp, your example of 15 universes really is not logical, Yes you can create multiple DP’s to get the data, but have you ever tried creating sections in your report on items from your DP’s that aren’t linked? Generally doesn’t work well and you need to be pretty good at report to resolve the problem.
Vikram, I really would like to know more about your problem, can you give me a bit more detail?
some qus comes in mind when if we go with large universe…something like 1000 table.
1)normally in 40 to 50 tables universe more then 500 objects created.so in this universe then something 10000 objects.is it good for a user?
2)and tables upto 3NF.means many fact tables may be used.something like 50…it means something 50 context.then how to know a user which context that will be use in the report?
3)what about performance?
I am new to this organization and have no idea what the users want. This requirement came from the Project Manager. I have gone over this debate more than 10 times and am still confused. I will gather all the information from this forum, create a document and give it to the PM. This way he can decide for himself the chosen path. Will post the doc here to.
What you need to develop is a vision for how you want the users to interact with the universe(s) you are building. There are lots of ways you can build your universe(s). Your constraints are the data model you are reporting against and the reports you are required to produce initially. You can choose a design with an eye to completing the required reports as quickly as possible. You can choose a design with an eye to long term maintainability. You can choose a design with an eye to ad hoc querying by power users. Regardless, you must have the vision. If you don’t have that vision, you are going to deliver a poor solution.
IMHO, you should design the universe with an eye on ad hoc querying by power users as I think that that design provides the greatest long term value to the corporation. Such a design includes everything useful in the underlying data model. Such a design takes longer and costs more.
Diamond, FYI, we had a discussion on the pros and cons of linked universes and the “Enterprise Universe Bus Architecture”, that Andreas described here:-
(BOB member since 2004-03-22)
(BOB member since 2003-09-17)
(BOB member since 2009-08-26)
(BOB member since 2002-06-20)
(BOB member since 2005-01-06)
.