Best way to import tables

[bi4business]

Hi All,

I am a little lost right now about the best way to import tables insight Framework Manager.

Some IBM Consultant tell me to import tables like:

select
  [field1] ,
  [field2] ,
  [field_#]
from
 

and other consultants tell me never use that, but use always: select * from because FM can handle this 'better' and the performance is better (I have not seen this, but oke...) What is the right way to import this ? BI4BUSINESS

[MFGF]

Hi All,

I am a little lost right now about the best way to import tables insight Framework Manager.

Some IBM Consultant tell me to import tables like:

select
  [field1] ,
  [field2] ,
  [field_#]
from
 

and other consultants tell me never use that, but use always: select * from because FM can handle this 'better' and the performance is better (I have not seen this, but oke...) What is the right way to import this ? BI4BUSINESS

The latter. This is why Select * from <table> is the default when you import table metadata (via the metadata wizard) from a database. It means the SQL can be minimized at runtime.

Cheers!

MF.

[navissar]

Opinions differ. Some developers I know would take the long way of naming each field in the select statement; others would go for select *.
Personally I prefer the all(*) method - it's cleaner, it generates minimized SQL, it deals better with fields removed (Only if the removed field is called upon by a model QS it will generate an error) and it's the default.

[Michael75]

Very interesting!

I'm on 10.1.1 with Oracle, and I've been importing a lot of tables & views into FM recently, using the metadata wizard. The SQL is initially in the form of

select
  [field1] ,
  [field2] ,
  [field_#]
from

and I manually change this each time to select * from in order to take advantage of minimised SQL.

I guess my question is,  is there really a default, or is there some other factor at play? I've just (re-)checked, and there's no FM governor for this.

Thx, Michael

[MFGF]

Very interesting!

I'm on 10.1.1 with Oracle, and I've been importing a lot of tables & views into FM recently, using the metadata wizard. The SQL is initially in the form of
and I manually change this each time to select * from in order to take advantage of minimised SQL.

I guess my question is,  is there really a default, or is there some other factor at play? I've just (re-)checked, and there's no FM governor for this.

Thx, Michael

Have you been selecting individual items within tables during the import, or checking the entire table? I suspect you have been doing the former, which will result in a qualified select for just those items?

Cheers!

MF.

[Michael75]

Have you been selecting individual items within tables during the import, or checking the entire table? I suspect you have been doing the former, which will result in a qualified select for just those items?

Thanks MF.

I always select the entire table. I've just run through the process again, which is:
1. Select a data source (Run DB query subject wizard is checked)
2. Database objects - navigate to a table or view and include it via the green arrow
The individual columns appear in the right hand pane, and after hitting next several times, I get the proposed SQL (see attached screenshot).

This behaviour is not a problem for me, and I'm asking more out of curiosity, but it would be a small time-saver for me if I could have the default behaviour.


Thx, Michael

[MFGF]

Thanks MF.

I always select the entire table. I've just run through the process again, which is:
1. Select a data source (Run DB query subject wizard is checked)
2. Database objects - navigate to a table or view and include it via the green arrow
The individual columns appear in the right hand pane, and after hitting next several times, I get the proposed SQL (see attached screenshot).

This behaviour is not a problem for me, and I'm asking more out of curiosity, but it would be a small time-saver for me if I could have the default behaviour.


Thx, Michael

What happens if you right-click a folder or namespace and select "Run Metadata Wizard" then work though this?

You should end up at a page that looks similar to the one below, where you can select the tables you require:



Doing things this way results in a "Select * from <table>" for each selected table.

Cheers!

MF.

[Michael75]

MF, you've nailed it! Although I didn't specify it, you guessed that my way of working was to

  - right click - Create - Query Subject - Data Source (Tables and Columns), rather than to
  - right click - Run Metadata Wizard

The dialogue is completely different, and doing an import "your" way I find the default behaviour you described earlier.

This forum is really gr8  ;D

[Michael75]

Just a small PS to the previous exchange. I found out today that, even if the goal is always to have query subjects whose definition is Select * from, one can need to use both techniques for importing metadata, i.e.

1. Right click - Run Metadata Wizard, which generates Select * from
2. Right click - Create - Query Subject - Data Source (Tables and Columns), which generates Select col1, col2,... col99 from

I had two view-based DB Layer QSs, defined as Select * from, from which I've just deleted a couple of columns in the views, and which I wanted to update in FM to reflect this change. OK, I thought, just Update Object on both of them and these columns will disappear.

I was half right. On one QS, this worked as planned, but in the other these columns just would not disappear. Thinking back to this exchange, I re-imported the view behind the problem QS using Create - Query subject, which gave me a New Query Subjext with a SQL definition which included all the columns, but not the deleted ones. I then took this SQL, applied it to my problem QS, which got rid of the unwanted columns, then changed its definition back to Select * from. Problem solved!

I mention all this in the unlikely event that it might, some day, be useful to someone  :)

[BigChris]

A useful reminder - thanks.

I had a bespoke SQL statement for one of my tables in my model, something along the lines of:

Select x.1, x.2, x.3, y.2, y.3
from x inner join y on x.1 = y.1
where x.2 > 50

It worked fine and brought back the right data...but it was slow. I took the SQL and created a view in my database and changed the model to be Select * from View. The performance improved significantly and made the reports that used that data much more usable. Like you, I'd been steered in that direction by a consultant...I'm now moving away and creating views where appropriate.

C