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
|
|
Quote from: bi4business on 07 May 2014 04:43:25 AM
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.
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.
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
Quoteselect
[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
Quote from: Michael75 on 07 May 2014 02:41:39 PM
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.
QuoteHave 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
Quote from: Michael75 on 08 May 2014 07:00:37 AM
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:
(https://image.ibb.co/hyEyLF/Import_zps342f2371.png)
Doing things this way results in a "Select * from <table>" for each selected table.
Cheers!
MF.
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
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 :)
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