CLOUD DATA PIPELINE PORTAL

Khaleelah

Dear ,

I am asked to use provided credentials on first login to the CLOUD DATA PIPELINE PORTAL.

However when using the credentials shown on the screen, the login fails. Even using my board credentials , authentication fails

Any idea please

Rgds

Helmut Heimann

Hi @Khaleelah ,

please, open a ticket with our support for that.

Best,
Helmut

Khaleelah

Thank you @Helmut Heimann

Khaleelah

hello @Helmut Heimann

I was able to successfully connect to Sql Database.

However in the schema tree, it seems im seeing only security schemas and not the schemas i should.

while on sql management studio i see

Im verifying internally also. However i would like to know if there is anything im missing on Board configuration.

Thank you

Helmut Heimann

Hi @Khaleelah ,

well, I think your next step should be to get back to the cloud admin portal and add/configure the datasource in the data pipeline section.

Did you do that?

Best,
Helmut

Khaleelah

Hi @Helmut Heimann

All these are done. Thats why i can connnect and see some schemas also. I did not specify and database so i should be having all the dbs, but im seeing only the security dbs.

This is from the admin portal

Helmut Heimann

Hi @Khaleelah ,

aha, ok.

Then, what happens if you click on one of those schemas? You say they are all security dbs.
In what schema does your desired database sit—none of these?

If you connect to the SQL server with Board's credentials, what do you see then in the Management Studio?

Best,
Helmut

Khaleelah

@Helmut Heimann

you mean to say i need to create the admin Portal Board User on the sqlserver. This is not done.

May be should create one and see.

Would it make a difference?

Helmut Heimann

@Khaleelah ,

it might be that you will need to create that user. That user should have credentials to work on the tables you want to extract.

Best,
Helmut

Bart Scott

It looks like you are missing proper security within SQL Server. The username that you are using to connect to SQL Server, needs to be set with permissions to access the Database(Schema) where you want to access, and access to the Tables (or views) within the Database with a minimum of read access to the tables. To test if the username/password is working correctly (and eliminate Board as an issue), go into management studio and log in with that username/password confirm you can expand the database, tables (or views) and run a query as that user.

Bart Scott
CFO Solutions LLC

Khaleelah

@Helmut Heimann

Yes, this is what we are going to do. It is not a schema user

Bart Scott

Yes it should be setup in the main SQL Security then granted access and a role with the correct privileges within the Database/Schema. Once it is fixed in SQL Server you should see the tables/views under the schema within Data Pipeline.

Bart Scott
CFO Solutions LLC

Khaleelah

Hi @Bart Scott

Actually in SSMS , i use the same user/password that i configured on BOARD Datasource and i can see the other DBs. But on Board i cannot.

What we are going to test is to add the Board portal admin as a user on the sqlserver and see how it works

Khaleelah

hi @Bart Scott

Yes this is what we are going to do.

Bart Scott

The only user the data pipeline uses is the username setup within the data pipeline connection. Database names are not shown within the data pipeline only the Schemas. Who owns the schemas in the database you are accessing? Many times it is dbo. Did you expand the Schema name used within the data pipeline to see if the tables/views are shown?

You can also do a quick test in a Query analyzer within Mgmt Studio. While logged in as the username/password, open a Query window with that user do a simple query like Select top 10 * from SCHEMA.TABLENAME and make sure you see data. Then copy and paste that exact query over to data pipeline test window and try to execute it.

Also ensure your database name is filled in on the first tab of the SQL Server Data Source in the data pipeline. You can also set the default schema within the advanced tab if you want to limit the schema Data Pipeline has access to.

Bart Scott
CFO Solutions LLC

Khaleelah

Hi @Bart Scott

Executed the query on both

On SSMS , i get table_catalogue as "ecl-mulesoft-staging-db-proj", on board i see only master and table_schema is only SYS

Bart Scott

Do this step as a Sys Admin. Within SQL Server, go into the main security, logins, double click on the user you are using within the Data Pipeline. Go to the User Mapping tab, and check mark the database where the tables/views exist. Ensure that is has db_datareader, or a role that gives it the permissions to access the appropriate tables. Save that. Make sure the Default Schema is correct (in my example all the tables/views within that database are owned by dbo).

Log in as User. Open a query window while logged in as that user, change the database to the proper database and run a test query, so for example on my setup:

select top 10 * from dbo.GL_Trial_Balance;

Then go over the Data Pipeline, and try to run the same query against the table/view:

select top 10 * from dbo.GL_Trial_Balance;

If that still doesn't work, try to add the db_ddladmin role, I do not remember if that is specifically needed or not from the Data Pipeline user.

Bart Scott
CFO Solutions LLC

Khaleelah

hi @Bart Scott

Finally was able to connect and see the tables. I have one question regarding the sql scripts that can be written. Is there a limitation as such?

For example

i can execute

select id,sales_group,segment,subsegment,company_chain
,row_number() over(partition by id order by sales_group desc) num
from dbo.table1
group by id,sales_group,segment,subsegment,company_chain

but below executes with error

with cte as (

select id,sales_group,segment,subsegment,company_chain
,row_number() over(partition by id order by sales_group desc) num
from dbo.table1

group by id,sales_group,segment,subsegment,company_chain

)
select * from cte

just to add on this, i can re-write the query but just wanted to know the limitattion

Bart Scott

Glad you were able to get connected. If you can share what solved the issue for you, it might help others in your same situation.

But you are correct, there are certain things that are not supported, or need to be done a specific way (for example comments in SQL script are not easy to do). I unfortunately do not know the answer to what is limited or unsupported, you may want to open a support case directly with Board.

Also from a best practice (and a performance standpoint), we tend to try and do views within SQL for anything complex, rather than building a complex query within Board/Data Pipeline. I.e your second SQL could be a view within the database side so then you just do Select * from OWNER.VIEWNAME within Board.

Bart Scott
CFO Solutions LLC

Khaleelah

@Bart Scott eventually we created a new user with the db_datareader role and i used a database name.

Before the database name was left empty. I think this solve the issue.

As for the queries, yes you are right its better to create views for complex meetings.

Thanks a lot for your guidance.

Khaleelah

@Bart Scott however are you able to pass parameters to the views like date range?

Bart Scott

Unfortunately it is not possible to send parameters directly to a data reader. When calling data readers within procedures you normally limit which data is being processed into cubes within the procedure itself (even though more data may be coming back from the query). There was an enhancement request at one point to add this as a feature.

Normally, try to limit the queries as dynamically as possible to reduce performance overhead. For example functions in the where clause to limit the data set to current year (or current year and prior year). Another possibility is to use the Bulk insert step in the Board procedure to write out to a parameter table what you want from the data reader, and the SQL statement in the data reader relies on what is pushed to that parameter table in SQL (but requires the data pipeline user to have write access granted).

Hope that helps.

Bart Scott
CFO Solutions LLC

Khaleelah

@Bart Scott this is a big limitation. I have data to be filtered by companies. so i need to pass the company code. Also i have date range.

Im not sure of being able to use it. :(

Bart Scott

I do not work for Board, but understand your concern. Depending on the use case there may be ways around it, but it would really depend on what you are trying to do. I would say to open an enhancement request again on the Enhancement community forum related to this.

Bart Scott
CFO Solutions LLC

Khaleelah

@Bart Scott yea will need to find workaround.

Thanks for all your support. Yea will open an enhancement request with Board.