Business Objects best practices when using Teradata

building semantic layer
1

Hi all,

Anyone please highlight of best practices when Business Objects is being used in conjunction with Teradata as part of a BI solution? Specifically, what architect/design decisions need to be made to ensure both performance and scalability?

Regards

bobxi :singapore: (BOB member since 2006-07-21)

2

As Per Gartner A ā€˜Star With Third-Normal-Formā€™ Hybrid Is a Data Warehouse Best Practice. Guys, Can any shed some light on how to go this approach in BO

bobxi :singapore: (BOB member since 2006-07-21)

3

We have built universes against the company Teradata DW, but I havenā€™t done or seen anything special for performance.

If you are going against a Teradata DW the the only thing I would suggest is putting a limit on the number of lines that the login can retrieve in a query, to prevent an unsuspecting user asking for 10,000,000 rows, which is quite common in a Teradata DW.

Oh I have just thought of one thing, the LOVs. I had to go in and edit the SQL on them to do a group by the field otherwise it just does a select distinct which can take ages, of course.

nwdb :uk: (BOB member since 2005-10-26)

4

Thanks nwdb for your inputs.

Is you teradata DW in 3NF ? if it is in 3NF
Do you build the universe directly on to of Teradata DW or do you created a separate layer like view where unverse is pointed.

bobxi :singapore: (BOB member since 2006-07-21)

5

We report using a variety of tables, but the fact tables are pre-calculated aggregate tables.

I think that the clue as to whether the database has any de-normalisation is in the name Data Warehouse. I havenā€™t looked everybodyā€™s databases, but I would expect some de-normalisation in any DW

nwdb :uk: (BOB member since 2005-10-26)

6

Its not like that, we are using teradata FS-LDM which is highly normalized. We know that reporting wont work effectively until it picks up data from denormalized tables. My question here is how to go about for denormalization whether to create view or temperory tables on top of the normalized DB. Also I want to know with respect to BO how to go about in designing universe in this scenario to improve the performance.

bobxi :singapore: (BOB member since 2006-07-21)

7

This would be the job of your DW architect to design the warehouse. There are different approaches that can be taken. We in our shop use Ralph Kimball methodology to design the warehouse. A warehouse, if designed properly, should denormalize the data.
To answer your second question, it is a vast topic to cover everything about designing a universe. However, to improve performance you can use index awareness in your universe.

Kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

8

Hi nwdb, can I ask you how did you manage to change SQL statement for LOV generation?
Due to that ā€œdistinctā€ is far from efficiency in conjuction with Teradata, I tried to use ā€œgroup byā€ clause for LOV generation.
Iā€™m taking following steps: opening properties window for given object ā†’ clicking Edit from Properties ā†’ from Query Panel Iā€™m selecting SQL View ā†’ in SQL Viewer Iā€™m putting appropriate SQL statement (simply using group by clause) ā†’ Iā€™m parsing statement and before closing Object Properties window Iā€™m checking ā€œExport with Universeā€.
But it seems that changes are not applied and not experted to the CMS.

Thanks in advance.

BTW, currently Iā€™m working on BOXI3.1.

lglinski :poland: (BOB member since 2009-11-30)

9

There is a Universe parameter within Designer for doing this:

DISTINCT_VALUES
DISTINCT_VALUES = GROUPBY|DISTINCT
Values GROUPBY|DISTINCT
Default DISTINCT
Description Specifies whether SQL is generated with a DISTINCT or GROUP BY clause in a list of values and Query pane when the option ā€œDo not retrieve duplicate rowsā€ is active.
DISTINCT: The SQL is generated with a DISTINCT clause, for example;
SELECT DISTINCT cust_name FROM Customers
GROUPBY: The SQL is generated with a GROUP BY clause, for example;
SELECT cust_name FROM Customers GROUP BY cust_name

anorak :uk: (BOB member since 2002-09-13)

10

[Moderator note - moved to Semantic Layer forum]

Nick Daniels :uk: (BOB member since 2002-08-15)

11

Thanks a lot anorak for reply!

Regards

lglinski :poland: (BOB member since 2009-11-30)

12

For what itā€™s worth, we just ran into a weird bug after our Teradata 13 upgrade. In order to work around it, we had to set the DISTINCT to GROUPBY as suggested above, and also set up the FORCED_SORT_LOV option to Yes. Itā€™s a bit confusing on that second one as I never saw an ā€œorder byā€ appear in the LOV sql, but it does work.

The bug was related to a distinct query (for an LOV) that included a Where clause with an In list with two values, along with a Teradata ā€œregression featureā€ that makes a DISTINCT query sort. In V13 the DISTINCT no longer includes a sort by default. The bug was quite nastyā€¦ running a query for that LOV would reboot the Teradata box. :blue:

Dave Rathbun :us: (BOB member since 2002-06-06)

13

Dave,
thx for sharing your experiances, more precious because related to Teradata :slight_smile: In Poland we still do not have many experts in this area. For sure I will keep in mind this bug and solution discovered by you.

Regards

lglinski :poland: (BOB member since 2009-11-30)

14

Dave,
:shock: :blue: Our DW is on Teradata 12 now, but weā€™re planning to move to Teradata 13 within the next 12 months. So this is something weā€™d want to test. I donā€™t think we have any LOVs with a WHERE clause with an IN LIST, but I assume the same issue could be caused by user-generated SQL. Can you post the SQL that actually caused the reboot?

Norm Rosen :us: (BOB member since 2002-07-10)

15

With TD13 a ā€œdistinctā€ no longer does an implied sort. TD provides a backwards compatibility setting which we had turned on. Without this, a select distinct query did not return a sorted list.

The SQL that caused the problem was simple:

select distinct table.col
from table
where table.col in ('A','B')

Thatā€™s it. If we changed the DISTINCT to GROUP BY it worked fine but did not sort. By changing the two parameters I mentioned in my last post we no longer generate DISTINCT queries, and the data is sorted with an ORDER BY, and our DBA team was able to reset the ā€œregressionā€ parameter to allow the more efficient TD 13 process to take place.

Dave Rathbun :us: (BOB member since 2002-06-06)

16

Norm, I provided more details in a blog post this morning.

Want to crash Teradata? Give it some LOVā€¦

I imagine by the time youā€™re ready to upgrade TD will have fixed the issue.

Dave Rathbun :us: (BOB member since 2002-06-06)

17

Hi Dave,

My company is upgrading to TD 13. We are currently using XI3.1 SP1 and I noticed that TD 13 is not supported. Since your last post on this thread was back in Feb, I am assuming you must not have been on SP3 either :wink:

Can you please highlight any issues going with TD 13 with BOBJ XI3.1 SP1, other than supportability and the ones that youā€™re already mentioned in the thread? Any advice that you can offer on this is highly appreciated.

Thanks,
Kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

18

Weā€™re on XI R2. I donā€™t remember the service pack. But other than the issues I outlined here and on my blog, weā€™ve been fine.

Dave Rathbun :us: (BOB member since 2002-06-06)

19

Thanks Dave! Really appreciate your response.

Kashif Saeed :pakistan: (BOB member since 2004-06-02)