Because you are in 3.x, the answers get a little more complicated.
You can base objects in a universe on a stored procedure. (I’m guessing that’s what you mean by “use stored procedure directly to design universe.”)
WebI reports really should use universes. That’s the way they are designed to work.
However, I believe that there is a way with WebI Rich Client to use the “local source” option to reference a stored procedure. You may need to do an intermediate step, though, like putting the results of the stored proc into a local file. I’m not positive on that.
Generally speaking, if you are finding yourself with a number of business cases in which you need to work around universes, you really should be using Crystal Reports instead. It just has a lot more strengths in that area, and will be much less of a pain about it.
according to this BOB post it is possible to create WebI reports from stored procedures. The original poster used XI R2 however later posters appear to have achieved it in 3.x
Having said that I have been attempting to both build universes and reports using stored procedures (MS SQL Server 2000 and 2005) in XI 3.1 without much luck although I can get DeskI reports to work intermittently.
I would be very interested to hear how you get on.
A dedicated reporting database will save you a whole load of pain later.
Reports will perform well, universe design will be easy and so on.
You can build all the stored procedure logic into your ETL process, get rid of your outer join woes and so on.
Thank you guys for your valuable inputs… I will talk to my PM regarding this.
I started from UI(Java) part to design Universe. Is it good??
coz from the front end they are showing me Input screens and letting me know that which tables affecting for each change/entry.
Like they have different set of modules like, M1, M2, M3, M4 etc…, Is it required for me to grow through with each and every module ??
If you have a ER diagram for the database that is being used by the application (in a lot of cases you can generate one using reverse engg capabilities of tools like ERWIN), that will be a good place to start with your analysis.
Once you figure out the structure of the database, you can then identify the tables you want to use depending on the data that is exposed in the front end (Java UI in your case) and take it from there.
Remember, building a logical model for the universe is just a starting point, you still need to collaborate with the users to figure out what is the best way to represent the data in the business model (classes, objects and conditions).
With all due respect, he has already stated that there are no FKs/PKs so he will probably struggle to reverse engineer the ERD.
BOCP,
I’d suggest throwing your efforts behind two causes - establishing reporting requirements and finding out who knows something about the underlying database. Only once you expose the complexities of getting data out of it can you justify to people why you need a reporting database. Obviously you’ve got the complexity of the stored procedures to refer to but you need people to understand that it will be easier in the long run to build the reporting database with good ETL routines.
From UI i got few test cases or i had a look on SQL Profiler for each step i made to UI and identified whats going on back-end…, like which table it’s refering and queries it generating.
Few of queries like follows:
Query -1 :
SELECT ENAME FROM EHEAD WHERE EID IN (SELECT FID FROM FHEAD WHERE eid= <>) /* SQL Profiler generated */
EHEAD and FHEAD are two tables and condition is on eid.
Now my questions , Can i join EID of EHEAD with FID of FHEAD in universe or am i going wrong.
Could you please help me out…
Query -2:
select ed, ename from ehd where sd=? and ccode=? and eid in (select distinct fid from glac) and eid in (select eid from usrdt where userid=?) order by entityname
(BOB member since 2009-09-23)
(BOB member since 2007-08-02)
(BOB member since 2005-01-06)