en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
SO  
#1 Posted : Friday, October 21, 2016 12:12:41 PM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 88

I have added a MS Access DB as a datasource. As default Targit inserts the SQL command Select * from [tablename]

I want to select multiple columns, not all. What is the command?

The reason why I want to do it is because there are so many columns (and rows) in the table that working in the data modeler with setting up relations and selecting which columns I need gets very very slow. Hopefully it will get faster if I select the columns via SQL command first.

This command I have tried:
select [tablename.columnname] from [tablename]

Edited by user Friday, October 21, 2016 12:16:00 PM(UTC)  | Reason: Not specified

SO  
#2 Posted : Friday, October 21, 2016 12:30:40 PM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 88

Update: If I added a new data source it worked with "select columnname from [tablename]", but if I tried to edit the datasource and change the SQL Command I get error message.

Why?
Páll Bjarkason  
#3 Posted : Monday, October 24, 2016 10:11:28 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 164

Hi SO

You have to use the correct SQL Syntax when selecting specific columns.

If you are not familiar writing SQL commands, you can eg. access an online resource.
This resource has some basic stuff on the select statement.
Quote:
Select column1,column3,column7 from [tablename]

http://www.w3schools.com/sql/sql_select.asp


Some updates have been made to the data source.
If all other fail you can delete the data source and recreate the data source using the correct SQL Syntax.
Usually I use SQL Server Management Studio when creating the SQL statement and then just copy-past into the TARGIT data source.
Best regards

Páll, CTS
SO  
#4 Posted : Tuesday, October 25, 2016 12:05:27 PM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 88

Originally Posted by: Pá Go to Quoted Post
Hi SO

You have to use the correct SQL Syntax when selecting specific columns.

If you are not familiar writing SQL commands, you can eg. access an online resource.
This resource has some basic stuff on the select statement.
Quote:
Select column1,column3,column7 from [tablename]

http://www.w3schools.com/sql/sql_select.asp


Some updates have been made to the data source.
If all other fail you can delete the data source and recreate the data source using the correct SQL Syntax.
Usually I use SQL Server Management Studio when creating the SQL statement and then just copy-past into the TARGIT data source.


My syntax was correct, but I was not allowed to change the sql query in a created data source. Had to delete the data source, create a new, paste the new query and save it again.

Is it supposed to be like this?

If I use the standard "select *" from a table in the datasource the data modeler module gets extremly slow when setting up relations etc. in the cube if there are many columns/rows (approx 250 colums x 200.000 rows in this case).

If I had used "select *" in the setup I get the possibility to add and remove fields in the table in the "cube editor" later, something which is a lot more flexible since I'm never 100% sure which fields I want to bring with me to the cube. But using select rows in the data source makes the cube module a lot faster and user friendly.
Peter  
#5 Posted : Tuesday, November 1, 2016 6:57:21 PM(UTC)
Peter
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/1/2011(UTC)
Posts: 43

You can add to the number of columns, however removing the columns will invalidate the meta data that will be stored and powers all related cubes.

With 250 columns in a table this will get to the point where you probably would be better off building a model manually (Check out the new TARGIT InMemory Database - you can read about it at doc.targit.com ;-))

Peter
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error