Hello, is it possible for Adobe Campaign access external database views instead of extrenal database tables. Currently I'm getting an error.

Avatar

Avatar
Validate 1
Level 2
tristanr1339829
Level 2

Likes

8 likes

Total Posts

13 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Applaud 5
View profile

Avatar
Validate 1
Level 2
tristanr1339829
Level 2

Likes

8 likes

Total Posts

13 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Applaud 5
View profile
tristanr1339829
Level 2

02-02-2017

Hello,

Is it possible to get access to external views of a database by FDA instead of tables ?

Currently I'm getting an error.FDA_TestConnection.PNG

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Coach
Employee
Vapsy
Employee

Likes

377 likes

Total Posts

733 posts

Correct reply

342 solutions
Top badges earned
Coach
Give Back 50
Give Back 25
Give Back 10
Give Back 5
View profile

Avatar
Coach
Employee
Vapsy
Employee

Likes

377 likes

Total Posts

733 posts

Correct reply

342 solutions
Top badges earned
Coach
Give Back 50
Give Back 25
Give Back 10
Give Back 5
View profile
Vapsy
Employee

02-02-2017

Hi tristanr13398296,

Keeping your requirement in mind we performed a simple test and here are our findings.

  1. Our local campaign instance is using PostgreSQL as database and the FDA is SQL Server with the version as SQL Server 2012.
  2. We attached a test database on SQL Server, the very famous AdventureWorks DB.
  3. For our test, we chose the table Person.Address. It has following columns. FDA table.png
  4. Created a View on top of it and named it dbo.vAddress. Its structure looks like as shown here:FDA View.png
  5. As you can see we have picked a subset of columns for our purpose.
  6. Using the FDA connection inside Adobe Campaign, we deployed a new schema based on the new view just created.FDA Schema.png
  7. Now, if we take a look at the structure tab it shows only the columns we had in the database view.View structure.png
  8. As soon as we switch to Data tab to see if the data is being extracted or not we get the following error. error.png

    The reason for this error is that Campaign assumes a view to being a table and hence it is requesting for a Primary key to be defined on it. Inside SQL Server, a view is not supposed to have a PK.
  9. To resolve this issue, we will perform a small hack. We will make campaign believe that there is a PK involved. To do so, in our view we have a column rowguid that is the primary key and hence inside schema we will make it be primary key of this view. Please mind, that we will make modification only on the campaign schema and not on the actual database view.hack.png
  10. Logout from the instance and log back in. This time if you preview the data the problem will be resolved.data.png

Hope this answers your query.

Regards,

Vipul

Answers (1)

Answers (1)

Avatar

Avatar
Validate 1
Level 2
tristanr1339829
Level 2

Likes

8 likes

Total Posts

13 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Applaud 5
View profile

Avatar
Validate 1
Level 2
tristanr1339829
Level 2

Likes

8 likes

Total Posts

13 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 1
Applaud 5
View profile
tristanr1339829
Level 2

07-02-2017

Thanks you very much, it has worked for my issue.

Cheers

Tristan