BusinessObjects Board

Columns in alphabetical order in Oracle Repository

building semantic layer
#21

This topic has been thoroughly discussed, but something has happened that makes me think it IS possible to easily change the sort order of the column names. I have 18 universes covering approximately 550 tables (Oracle database) that have been in development for several months … carefully built with ColSort=13 to have the column names in database order. Now, they have ALL reverted to column names in alphabetical order!

Since we are nearing deployment, there has been a lot of activity with tiny tweaks and near daily exports, so I can’t pinpoint exactly when it happened. I am rather sure it had to do with upgrading to 5.1.7, since it replaced the Oracle.sbo file which has ColSort=3, and I forgot to reset to ColSort=13 right away.

Has anyone else experienced this? Any idea what sequence of steps causes it? If I can pinpoint it, I’d obviously like to do them in reverse order to get 'em all back!

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#22

You can do this for other databases if you use the external strategy method. I just did this for teradata tables so the SQL would have to be changed for SQLServer but this could get you started


[STRATEGY]
TYPE=STRUCT
NAME= Alphabetize table columns
[SQL]
SQL=
SELECT TRIM ( databasename ) , 'ƒ' , 
TRIM ( databasename ) , 'ƒ' , 
TRIM ( tablename ) , 'ƒ' , 
TRIM ( columnname ) , 'ƒ' , 
( CASE WHEN columntype  IN ( 'c' , 'cv' ) 
THEN 'C' WHEN columntype  IN ( 'I' , 'I1' , 'I2' , 'D' , 'F' ) 
THEN 'N' WHEN columntype  IN ( 'DA' , 'TS' ) THEN 'D' END ) AS datatype , 'ƒ' , 
'Y', 'ƒ' 
FROM dbc.columns ORDER BY databasename , tablename , columnname 
WHERE Databasename LIKE ('PROD%') or
databasename like ('DW%');

[HELP]
Help- This strategy will order the column name alphabeticly for tables in PROD% and DW%

The column delimiters look ‘funny’ in the code above but check out the delimiter already used in your ST****EN.TXT file and substitute where there is ‘ƒ’

Michele Pinti (BOB member since 2002-06-17)

#23

I’m on Oracle not SQL Server … and I want to “unalphabetize” mine (reset to database order), but I get the idea … very slick actually. If I’m not mistaken though, the problem is the same. The strategy is only applied when the table is added to Designer, right? For Oracle (and Informix, there is a ColSort parameter in the *.sbo file that allows me to control the order when tables are added. My need is to reorder the column names for existing tables.

I still can’t explain why mine reverted, but I’m actually close to an automated solution to fix it …

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#24

Yes you are correct. I think my post was more of an answer for Lee Drakes question.

Michele Pinti (BOB member since 2002-06-17)

#25

Ahh, my apologies. I forgot that I had resurrected an old thread :oops:

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#26

Eureka! OK, first the short version. In a VBA project, set a reference to the Designer library, and use this code:

Sub ResetColOrder()

    Dim DesignerApp As Designer.Application
    Dim Univ As Designer.Universe
    Dim Tbl As Designer.Table

    Set DesignerApp = New Designer.Application
    DesignerApp.Visible = True
    Call DesignerApp.LoginDialog 'XI syntax
    'Call DesignerApp.LoginAs 'v5/v6 syntax
    Set Univ = DesignerApp.Universes.Open

    For Each Tbl In Univ.Tables
        If Not Tbl.IsAlias Then
            Tbl.Columns(1).Delete
        End If
    Next Tbl
    Univ.RefreshStructure

End Sub

Now the long version, or “why does this work?” Many ideas early in this thread tried to get the “Refresh Structure” function to reset the column order according to the ColSort setting in the *.SBO file. Yes, that works. The trouble is getting the Refresh Structure function to actually do something. Changing owner to something bogus doesn’t work. And if there isn’t REALLY a change, it doesn’t work.

The code above removes the first column from the table structure, something possible with the object model (VBA code), but apparently can’t be performed manually. Note that this is only a tweak to how Designer has the table defined. All objects, joins, contexts, and so forth are unaffected … even joins on a removed column remain intact. Then the Refresh Structure finds that there is a change, puts the one column back in the table structure, and along the way re-executes the ColSort routine!

Life is pretty darn good sometimes :yesnod:

{03-Mar-2009 … updated code example for XI syntax … dwayne}

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

BO XIR2:Sorting of objects in the universe
Alphabetizing table fields with BOXI Designer
Columns sorting in XIr2
Data base columns are sorted in alphabetic order in Designer
6.5.1 Column order in the structure view
#27

Dwayne: the BusObjects VBA guru :yesnod:
Great update!

Andreas :de: (BOB member since 2002-06-20)

#28

I’m also a bigot on NOT using VBA unless it’s necessary, but when all else fails, it can come to the rescue!

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#29

Thanks for the update. I can definitely use this! Our prototype tables are not in alpha order and I forget to use the external strategy when I insert them so to correct them I have had to reinsert them.

Michele Pinti (BOB member since 2002-06-17)

#30

Hope it works for you! I know that the technique picks up the ColSort parameter in the *.SBO file used by Oracle / Informix. Since you’re on Teradata though, not sure if the external strategy is also applied again, but that would good information to know.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#31

If you are running Oracle 9i, I find the easiest thing is this:

  1. As mentioned in the replies above, ensure you have colsort changed to your desired value (13 for most) in the oracle.sbo file.

  2. Create a script to add a new column to each of your database tables.

alter table customer
add temp varchar2(1);

  1. Refresh your universe in Designer. At this point you should notice column order has changed.

  2. Create a script to remove the temp column from each of the tables.

alter table customer
drop temp;

*** Warning *** My understanding is that you cannot drop columns from a table in oracle versions prior to 9i. :frowning:

Altering the length of a column may work instead, but I can’t say I’ve tried it.

  1. Refresh your universe again in Designer.

I hope this helps, particularly for those who have a lot of tables to update.
8)

The Emu :australia: (BOB member since 2002-09-13)

#32

Hi Dave,
I have some universes where it seems somehow the columns are back in alphabetical order rather than in column order. Is there any way to set them back to column order? Or do I need to take them all out and re-import…if so, how to I make sure that I don’t lose all my object defnitions?

Thanks

Peter

peternolan9 (BOB member since 2003-02-01)

#33

Peter:

Did you see this post a bit earlier in this topic?

Dave

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

#34

Hi Dwayne,
I tried appending to an old thread first but got no answer over a few days so I tried again…I have been really frustrated by this…I think my universes might have gotten damaged when I had to rebuild my laptop from scratch courtesy of a win2000 crash…

Anway, I am sorry to be so ignorant, but when you say:
‘In a VBA project, set a reference to the Designer library, and use this code:’

What do you mean?
I never use VBA and I don’t know what the ‘Designer Library’ is. (If I need something in code I write C++ nowadays.)

A VBA project in what? I have VS.net which includes VB.net. Or do you mean in designer somehow?

I certainly hope we can ressurect my universes…:slight_smile:

Best Regards

Peter Nolan

Code:
Sub ResetColOrder()

Dim DesignerApp As Designer.Application 
Dim Univ As Designer.Universe 
Dim Tbl As Designer.Table 

Set DesignerApp = New Designer.Application 
DesignerApp.Visible = True 
Call DesignerApp.LoginAs 
Set Univ = DesignerApp.Universes.Open 

For Each Tbl In Univ.Tables 
    If Not Tbl.IsAlias Then 
        Tbl.Columns(1).Delete 
    End If 
Next Tbl 
Univ.RefreshStructure

End Sub

peternolan9 (BOB member since 2003-02-01)

#35

I hope I can clear things up for you. VBA (A = for Applications) needs a “host” application. It is not a standalone programming environment like true VB or C++. Currently, Designer itself does not function as host for VBA, therefore you need some other application as a host. You can use Business Objects or Excel for example, the technique is the same.

From that application (I’ll assume Business Objects), start the VB Editor (Alt-F11 is the quickest). At this point you can use the Business Objects COM (common object model), but not Designer. From the menu, select Tools, References… You will see a few items checked at the top of the list, followed by a very lengthy list of other object models that are available to you. Scroll down to the Business Objects Designer Object Library item, check it, then click OK. At this point you can now utilize the Designer object model, even though you are hosted by Business Objects.

Insert a module, copy in the code above, and you should be good to go.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#36

Hi Dwayne, (and any lurkers)

Thank you very much for your help. It is GREATLY appreciated. :smiley:

When I ran the code you suggested I got the message.

Run-time wrror ‘-2147418111 (80010001)’ Automation error.
Call was rejected by callee.

On the line “For Each Tbl In Univ.Tables”. no idea why.

I changed the code a little to as follows. I think providing the uiniverse name in full was what got rid of the automation error…maybe it will help someone else who sees this error.

Sub ResetColOrder()

Dim DesignerApp As Designer.Application
Dim Univ As Designer.Universe
Dim Tbl As Designer.Table
Dim i As Integer
Dim j As Integer

Set DesignerApp = New Designer.Application
DesignerApp.Visible = True
Call DesignerApp.LoginAs(“supervisor”, “password”, True, “XYZBOREP01”)
Set Univ = DesignerApp.Universes.Open(“D:\Program Files\Business Objects\BusinessObjects 5.0\Universe\TUFC402.unv”)

j = Univ.Tables.Count

For i = 1 To j
If Not (Univ.Tables(i).IsAlias) Then
Univ.Tables(i).Columns(1).Delete
End If
Next i

End Sub

peternolan9 (BOB member since 2003-02-01)

#37

Glad you were able to get it to work!

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#38

Hi All,
after I have editted my universes and updated/refreshed etc I tried to put them back into the repository. But the import/export actions are greyed out!!! (Never seen that before…)

I am wondering if it is because I said that the edit via the VBA should be disconnected?

Anyway, if the import/export actions are greyed out, how do I put the universes back into the repository? Also, when I tried to publish a report to the repository the publish to->corporate repository was also greyed out.

I hope I can keep the changes I made!!!

Any advice most appreciated!!!

peternolan9 (BOB member since 2003-02-01)

#39

My most sincere and humble apologies for the last post…I just noticed that the ‘use in offline mode’ box was checked in designer…

Been looking at too many complex universes for too long :shock:

Sorry.

peternolan9 (BOB member since 2003-02-01)

#40

Dwayne,
just as a related question. Is the object model for designer available via C++ classes?

Reason for my question is I used to have some code to update the BO universes directly using ODBC and MS Access.

I have recently written a suite of C++/ODBC classes as the foundation of an ETL tool I built for myself…It would be really useful for me to build a tool that took information from the PowerDesigner repository and updated the BO repository but do it thru support classes rather than directly. (which is clearly unsupported…)

Thanks

peternolan9 (BOB member since 2003-02-01)