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: Upcoming Events: PGHBOUG: Nov 1.

Things you should never do - a collection and discussion
1 members found this topic helpful
Goto page Previous  1, 2
 
Search this topic... | Search DI: Designer and Job Design... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Author Message
mrunals
Senior Member
Senior Member



Joined: 27 Dec 2006

Posts: 38



PostPosted: Thu Jun 16, 2011 6:19 pm 
Post subject: Re: Things you should never do - a collection and discussion

@agulland
Adding to your point, it is also important create a database alias. If the database owner name is different across the Dev/Integration/QA/Prod envs then it is very important to create an alias and then assign the owner names to the alias created in the Configuration settings of a data store.
Back to top
agulland
Principal Member
Principal Member



Joined: 17 Mar 2004

Posts: 474
Location: UK


flag
PostPosted: Mon Jun 20, 2011 6:06 am 
Post subject: Re: Things you should never do - a collection and discussion

yes, very good point mrunals. And then don't hard code alias name in SQL, rather do something like,

Code:
# Get databasename and schema name
$vDatabaseName = db_database_name('DS')
$vSchemaName = db_owner('DS','ADMIN');

# Execute SQL
sql('DS','select * from [$vDatabaseName].[vSchemaName].MY_TABLE ');


This isn't always necessary and sometimes using two datastores (for each alias) would be a more manageable solution.

AL

_________________
AL Gulland, BI Consultant

Further BI articles and reading @ www.gulland.com/wp
Back to top
rajnia1
Senior Member
Senior Member



Joined: 17 Nov 2011

Posts: 90



PostPosted: Tue Nov 22, 2011 2:11 pm 
Post subject: Re: Things you should never do - a collection and discussion

There are cases where there are specifications that the bodi job has to trigger another job(cmd command on scripts) only on a particular host/ trigger mail if the job runs on a specific server.

In those cases, the conditions for host name is mentioned on bodi scripts.
I have witnessed in years older jobs that those host names were hard coded in scripts like below,

If host_name='ukshutts01'
(
<trigger mail>
)

The jobs were huge and when I drilled , it went on and on and on.
In future, if we need to have change in the host name , it would be so difficult .
Hence it is better to have the host names onto a global variable and mention them on the initialise script.

If change needs to made, it can just be made just oncce.

I am sharing this piece of info as I am currently facing the task of drilling jobs and modifying the new host names. Its really hectic.

Hence better to use global variables as and when they are required.
Back to top
agulland
Principal Member
Principal Member



Joined: 17 Mar 2004

Posts: 474
Location: UK


flag
PostPosted: Thu Dec 08, 2011 5:35 am 
Post subject: Re: Things you should never do - a collection and discussion

yes good point on host names. i guess coding like this is because we want to send the mail when in production rather than dev, test? So suitable code would be,

Code:
if current_system_configuration() = "production" then
   send mail
else
   print message


or have other global variables that control email vs print message that can be set at runtime

AL

_________________
AL Gulland, BI Consultant

Further BI articles and reading @ www.gulland.com/wp
Back to top
ds41user
Principal Member
Principal Member



Joined: 03 Sep 2007

Posts: 150


flag
PostPosted: Sat Jun 30, 2012 8:38 am 
Post subject: Re: Things you should never do - a collection and discussion

Quote:
Approach: DataServices is so limited when it comes to the sql syntax supported in Queries, I do not want to read all data from a table, I want to use variables to read just a subset

Solution: I use a SQL Transform everywhere

Problem with that: A SQL Transform has lots of limitations, no impact lineage information, no partitioned readers, hard to read & modify the SQL statements typed, no full pushdown in the sense of an insert..select.., etc.


Agree to this a million times...

The problem always starts when the output of SQL transform is getting Inner/Outer joined into another Query.
The optimizer didn't knew what was coming from SQL transform and hence it produces Cartesian products of no. of rows even if the final no. of rows expected are in hundreds.

10 Query transforms to achieve the output of one SQL transform should always be the preferred way of designing jobs icon_smile.gif icon_smile.gif icon_smile.gif
Back to top
ds41user
Principal Member
Principal Member



Joined: 03 Sep 2007

Posts: 150


flag
PostPosted: Sat Jun 30, 2012 8:56 am 
Post subject: Re: Things you should never do - a collection and discussion

Approach: Since there is a common table to be read multiple parallel Query transforms (which aren't linked to each other) , why to use multiple table source readers? It also is creating multiple queries within the 'View Optimized SQL'.

Solution: Just use one Table reader as a source for all the queries and the performance will be improved. After all, only one query needs to be fetched and DI will use it for all the Query transforms.

Problem with that: Offcourse there is just one SQL which is being sent to the database, but that one sql has to serve as a source for all the parallel Query transforms.
This will produce multiple "CacheSplitMemoryReader"s when the DS runs the DF.
The where clause may be different for all the Queries, which again makes it imperative for them to be pushed down to the database, so that only the required no. of rows for each Query transform get pulled.

Use of a separate reader for each Query transform makes sense.
Back to top
philmorris
Principal Member
Principal Member



Joined: 12 Nov 2002

Posts: 178
Location: Midlands - Uk


flag
PostPosted: Tue Mar 18, 2014 4:19 am 
Post subject: Re: Things you should never do - a collection and discussion

Hi,


I would suggest some notes and guidance on initial setup around repositories, developer workstations, job servers and the like.

It seems every project I have worked on lately has developers using their workstation to connect to remote repository databases and servers, often via firewalls, VPNs and the like.

To me, if you have use cases where your developers need to work in this way, or even just if the database platform is crowded, slow and not very responsive, then you should be installing repository databases locally. If licenses allow then job servers likewise.

In addiiton, it seems that the server codepage settings are regularly ignored: having a UTF16 setting on the server when all datasources are UTF8 really does not help!.

Edit: Another point on repository databases - It is a mistake to have any form of password expiry on these databases, or automated session or connection cleanup. At least read-only access directly to the DB should also be granted to the developers, so their efforts can benefit from the rich metadata within.
The sheer pain of logging into Management Console when there are many expired repositories is something to behold. Developer also has some hefty pregnant pauses and abnormal terminations when it finds its connections to the repo db have been killed.
I've heard it said many times, "BODS is soooo sloooow!", but I've only ever found this to be the case when simple mistakes have been made in the deployment.

_________________
Phil Morris
Technical Director
Black Rose Solutions Ltd.

Business Intelligence
Data Integration, Governance and Quality Mangement
Database and Applications
...and everything else IT!
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Page 2 of 2 All times are GMT - 5 Hours
Goto page Previous  1, 2
 
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.0288 seconds using 17 queries. (SQL 0.0023 Parse 0.0009 Other 0.0257)
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