BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: No events within the next 45 days.

Is it possible to use a table-valued function in a universe?


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
ethan1701
Principal Member
Principal Member



Joined: 05 May 2004
ASUG Icon
Posts: 334
Location: Israel


flag
PostPosted: Tue Apr 28, 2015 9:44 am 
Post subject: Is it possible to use a table-valued function in a universe?

SQL server supports both scalar functions and table-valued functions.
I know BusinessObject supports scalar functions, but from what little I've read and tested, table-valued functions are far more efficient (see here).

So, is there any way to call a table-valued function in BusinessObjects?
The example in the link above uses
Code:
cross apply
to connect the function to a table. I can't imagine how this might be accomplished in Designer/ IDT.

I'd love your help.
Thanks!

_________________
BusinessObjects Enterprise XI 4.1 SP6
Windows 2008
SQL Server 2014
Tomcat7
Back to top
Mak 1
Forum Aficionado
Forum Aficionado



Joined: 06 Jan 2005

Posts: 13981
Location: London


flag
PostPosted: Tue Apr 28, 2015 10:28 am 
Post subject: Re: Is it possible to use a table-valued function in a unive

Does not look possible, according to what looks like a copy of a SAP note.

http://www.botrainingonline.com/sap-business-objects/microsoft-table-valued-functions-universe/

_________________
Next Available for Contract Work - January 2018

Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
ethan1701
Principal Member
Principal Member



Joined: 05 May 2004
ASUG Icon
Posts: 334
Location: Israel


flag
PostPosted: Tue Apr 28, 2015 12:04 pm 
Post subject: Re: Is it possible to use a table-valued function in a unive

That's too bad, but thanks for finding that!
I guess I'll add that as a feature request.

_________________
BusinessObjects Enterprise XI 4.1 SP6
Windows 2008
SQL Server 2014
Tomcat7
Back to top
charlie
Forum Associate
Forum Associate



Joined: 20 Aug 2002

Posts: 880
Location: Woodinville, WA USA


flag
PostPosted: Mon Apr 15, 2019 2:44 pm 
Post subject: Re: Is it possible to use a table-valued function in a unive

Mak 1 wrote:
Does not look possible, according to what looks like a copy of a SAP note.

http://www.botrainingonline.com/sap-business-objects/microsoft-table-valued-functions-universe/


Sorry, I couldn't access that link so my question could be moot.

I have a fact table of sorts with columns for values (e.g., airfare, car rental, entertainment, etc). The column name for each of these values is what I would normally have as something like "Expense_Category", but this table is flattened from aggregate data (with one row for each client and Quarter), and there is no associated dimension table for the expense category. I've used a CROSS APPLY in SQL to unpivot the data, but does anyone know of any tricks to do the same thing in a universe or WebI? I'd rather not have to use a derived table to unpivot if it can be helped.

_________________
Regards,

Chaz
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11382
Location: Manchester, UK


flag
PostPosted: Tue Apr 23, 2019 5:08 am 
Post subject: Re: Is it possible to use a table-valued function in a unive

Consider using a materialised view or just a view - pushing it back to that layer should put onus on the DBA to tune it because it's then in their sphere of visibility. You'll then be able to build objects as normal when adding the view to your universe.
_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
charlie
Forum Associate
Forum Associate



Joined: 20 Aug 2002

Posts: 880
Location: Woodinville, WA USA


flag
PostPosted: Wed Apr 24, 2019 10:27 am 
Post subject: Re: Is it possible to use a table-valued function in a unive

Yep, that's what we decided to do icon_wink.gif. They will use my derived table code for that view. Keep the pivoted table as is, and add the unpivoted.
_________________
Regards,

Chaz
Back to top
richardcottave
Principal Member
Principal Member



Joined: 30 Mar 2006

Posts: 297



PostPosted: Thu Apr 25, 2019 8:30 am 
Post subject: Re: Is it possible to use a table-valued function in a unive

Seems like I remember creating a SP universe which used table variables not table value functions but I abandoned it because of other limitations such as no LOV's and you can't have other objects in the universe.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0338 seconds using 17 queries. (SQL 0.0080 Parse 0.0009 Other 0.0249)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo