2 Replies Latest reply: Jan 26, 2018 11:10 AM by Mickael Briet RSS

    SQL UDC based on views

    Mickael Briet

      Hello community,

       

      is it possible to configure an user directory connector based on 2 views?

      I've got this issue when I apply my configuration :  Error when validating tables: One or more of the expected tables QS_USER and QS_USER_ATTRIBUTE are not present.

      QS_USER and QS_USER_ATTRIBUTE are 2 views with this schema :

      Capture.PNG

      QS_USER_ATTRIBUTE is empty for the moment.

       

      Thank you for your answer,

      Mickael.

        • Re: SQL UDC based on views
          Levi Turner

          Sure.

           

          Sample code:

          -- Create the database if needed
          CREATE DATABASE QlikUsers;

          -- Create table basic user table
          USE [QlikUsers]
          GO

          CREATE TABLE users_basic (
          userid varchar(255) NOT NULL,
          name varchar(255)
          );

          -- Fill in user info
          USE [QlikUsers]
          GO

          INSERT INTO [dbo].[users_basic]
          ([userid]
          ,[name])
          VALUES
          ('jhdoe', 'John Doe');

          -- Create table basic attributes table
          USE [QlikUsers]
          GO

          CREATE TABLE attributes_basic (
          userid varchar(255) NOT NULL,
          type varchar(255),
          value varchar(255),
          );

          -- Fill in attribute info
          USE [QlikUsers]
          GO

          INSERT INTO [dbo].[attributes_basic]
          ([userid]
          ,[type]
          ,[value])
          VALUES
          ('jhdoe', 'Role', 'foo');

          -- Create user view
          CREATE VIEW [user_basic_view] AS
          SELECT userid, name
          FROM [dbo].[users_basic];

          -- Create attribute view

          CREATE VIEW [attributes_basic_view] AS
          SELECT userid, type, value
          FROM [dbo].[attributes_basic];

          Sense Configuration:

          kmgh5u6.png

          • Re: SQL UDC based on views
            Mickael Briet

            Hi Levi,

             

            thanks for your answer,

            issue was the attribute table, defined with varchar(0).

             

            UDC is working well now.