Stored Procedures in XI R2

building semantic layer
1

Hi,

How can we access Stored Procedures in XI r2 Designer as a data source? Can we use Derived Tables for the same?

I am using Sybase DB.

Please guide me on this.

Warm Regards
Amar

amarnatha :india: (BOB member since 2006-05-08)

2

There is no Stored Procedure option in XIR2 Universe Designer. But it is there in XIR3.

Derived Table and SP are not the same. Derived table is more like a database view and is not a replacement for SP. Iā€™d suggest you to use Derived table only if it is not possible to create a database table/view.

If you are using Desk-I or Crystal, you may use Stored Procedure directly from these client tools.

shamanth (BOB member since 2007-03-01)

3

You can use a stored procedure in Web-I as well, albeit with a bit of trickery.

Is it nessecary for you to use your stored procedure as a data source? Are you trying to join itā€™s result set to another table or view? If so, this method wouldnā€™t really help you, but if you simply want to return the result of a stored procedure to Web-I document then I can try and explain how.

Before I do, please let me know in what capacity you are trying to use your stroed procedure. (it is a lengthy explaination).

Ryan Bierschenk :us: (BOB member since 2009-01-26)

4

There is no way to use any other source other than Universe in Web Intelligence XI R2.

Ryan,

Can you please explain your process? How are you going to pass the parameters to a SP from Web-I or Universe in XI R2!! As far as I know there is no way to do this. What is your trick?

-SG

shamanth (BOB member since 2007-03-01)

5

The trick, in a nut shell, is to use dummy dimensions that share the same data type as those returned by your stored procedure. Then you simply use custom SQL in WEBI to comment out the auto generated SQL and add the ā€œexec sto_nameā€ command. You also need 1 real dimension as well, or the SQL wonā€™t parse. For instance:

Your stored procedure returns 4 columns of data; int, int, varchar, date

In your universe that connects to the DB server where the stored procedure resides, create 3 new objects (one as a ā€œNumberā€, ā€œCharacterā€, and ā€œDateā€). The select command in each object will remain blank. Save. Export.

In WEBI, drag those 3 dummy dimensions into your query panel as well as the one real dimension that also has an int data type. This will autogenerate SQL under ā€œCustom SQLā€. In the ā€œCustom SQLā€ editor, you can comment out the generate SQL using /* */ tags. Add the ā€œexec sto_procedure_nameā€ below the commented section and VIOLA! Stored Procedure use in WEBI. You will also need to add the ā€œset nocount onā€ before the commented field because WEBI R2 doesnā€™t like comments to be the first thing encountered. You SQL should end up looking something like:

set nocount on
/*
SELECT table_name.int_field
  ,
  ,
   
FROM
table_name
*/
exec sto_incentive_mso_summary_report_q1

Your 3 dummy fields show up as blank lines above as well as your real field from the real table. Since the SQL is commented out, it will not run. It is simply a placeholder for what WEBI is expecting in returning data. Give it a shot and let me know how it works for you!

FYI, this isnā€™t just theoretical, we have mutliple reports in production that use this method. :smiley:

Ryan Bierschenk :us: (BOB member since 2009-01-26)

6

Hi,

I am facing similar issue. Need to use Stored Procedure in Universe Designer.

Product - BO XI R2
Database - Sybase 12.5

I can convince them to upgrade to BO XI 3.x as it is having new feature to build Universe directly from Stored Procedure. But I am not very familiar with this feature. So need your suggestions.

In the existing Stored procedures, there are couple of #temp_tables. Is there any known issue if using Stored Proc. with #temp_tables & Variables while building Universe/Webi reports :?:

I donā€™t have rights to create Tables/Viewsā€¦so is there any alternative way to use #temp_tables?

Please share your thoughts. Thanks.

nicholas (BOB member since 2008-07-31)

7

We are using BOE XI Release 2, talking to both SQL Server and Oracle databases and using Crystal Reports for reporting.

This is my first BOE project and I am trying to figure out how to do things (having been on introductory BOE and Crystal courses).

I need to call a stored procedure from within a BOE Universe that is connected to SQL Server. The SP takes a single parameter and returns a number of rows and columns.

Is this possible? This seems to be the thread that should answer the question and my reading of it implies that it is not possible in BOE XI-2 but may be possible in BOE XI-3. Even then, it is not clear whether this can be done within a normal universe or whether I would have to create a special ā€˜stored procedureā€™ universe.

Clarification would be most helpful!

ā€” Alistair.

Alistair :uk: (BOB member since 2010-07-09)

8

Hi Ryan,

I was trying to run Stored procedure in infoview after going through your
work around. I tried the way you had said but itā€™s not working for me.

Is there any XIR2 specific Serivce Pack needed to run the SP?

Or is there any other steps which I need to implement apart from what you had said.

Your inputs will be appreciated.

Thxā€¦

amitvani (BOB member since 2008-10-30)

9

A late answer but maybe it can help:

on Sybase ASE, you can access the resultset of a stored procedure through a ā€œexisting tableā€:

create existing table () external procedure at ā€œlocalserver...<stored_proc>ā€

using this, you can access the resultset in the Universe, using the existing (remote) table.

Bit tricky sometimes but it works :slight_smile:

Schtroumpf :belgium: (BOB member since 2006-09-01)

10

I have a stored procedure that returns data in a table-type format. I call the procecure in a derived table creation-statement like this:

 Select E.ID, D.colA. D.ColB, D.ColD FROM TestTable E, TABLE( MY_REPORT_DATA.GetMyData( E.entity_id)) D

The data that is extracted by the stored procedure is actually stored in the database in a compressed blob format.
The stored procedure in its turn calls on some C++ code that understands the blob formatting and can unpack the data and return in a format suitable for reporting.

Environment: BOBJ XIR2 and Oracle 11g

goodwinma (BOB member since 2010-11-15)

11

I may be the late entrant in this thread. But it was a really usefull thread.
But I have couple of questions.
I have created a stored procedure Universe. Now to display the required result set how do i need to proceed.

I was trying Ryan Bierschenkā€™s approach do I need to have a display result set in the stored procedure itself(select statment on the table).
Or create a derived table. Or how to go about it.

In nut shell how do I return the data set???

I hope I made my self clear.

Thanks,
Madhur_bob

madhur_bob (BOB member since 2011-04-08)

12

Ryan Bierschenk is describing how to call a stored procedure directly from Webi, by modifying the SQL code directly in the report (ā€œCustom SQLā€).

I on the other hand am callling my stored procedure in the universe as described in my entry just prior to yours. The procedure call is part of a derived table sql-statement. I do this because I have a ā€œset of structuresā€ returned by the stored procedure, thus my derived table has multiple columns and will also consist of multiple rows. Some of the columns in my derived table also needs to be linked to other tables in the universe - calling the stored procedure from Webi directly as Ryan describes would not work for me.

Malin

goodwinma (BOB member since 2010-11-15)

13

So to summarize I will do the following steps:-

  1. Create a stored procedure Universe.
  2. In the same Universe will use a derived table to select the contents from the table.
  3. export the universe.
  4. for reporting formation will drag and drop all the given columns.

Please let me know if my above understanding is correct.

Thanks in advance!!!

madhur_bob (BOB member since 2011-04-08)

14

Request u all to please respond to the above postā€¦

I am stuck at this last stepā€¦ to complete my project workā€¦

Please respond!!!

madhur_bob (BOB member since 2011-04-08)

15

Madhur_bob,

Sorry I am so late replying to your post. Have you found the solution you were looking for? If not, creating a stored procedure universe in XIR3 works as follows:

  1. Ensure your stored procedure is set up and the last executable query in your stored procedure selects the data set you wish to return.
  2. Create a stored procedure universe and add your stored procedure as a source.
  3. Drag over the fields from the source to create your dimensions/measures
  4. Save/Export
  5. Build WEBI report as you normally would with any other universe.

Let me know if that help or if you need more detailed clarification.

Ryan Bierschenk :us: (BOB member since 2009-01-26)

16

Thanks a lot Ryan for such a detailed replyā€¦

I have one last question ā€¦ I am almost thereā€¦

The stored procedure which I am using have lot of dynamic table(tables appended with the month.). So for every monthly report , the stored procedure needs to be executed first then we can get the result displayed for that particular month.

I see that on building the universe the Sp get executed.

How will this thing happen while making monthly reorts.
When/How will the sp will get executed for new results every month.

madhur_bob (BOB member since 2011-04-08)

17

With a stored procedure universe, all you need to make sure of is that the final query in the stored procedure returns the data that you want to show on the report. You can force that query to return which ever period of data you want (most current month, most current week, etc.) by simply using the SQL that is appropriate.

I am not sure I complete understand your full requirement, so please elaborate if my comments above still havenā€™t helped you.

HTH

Ryan Bierschenk :us: (BOB member since 2009-01-26)

18

I tried to use the method mentioned by Ryan. it says invalid number of columns. do i need to change the format? I am using XIR2.

Kiran challapalli :india: (BOB member since 2006-09-15)