topic Re: SQL UDC based on views in Management & Governance

SQL UDC based on views

mickael_weqan — Tue, 23 Jan 2018 16:33:37 GMT

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 :

QS_USER_ATTRIBUTE is empty for the moment.

Thank you for your answer,

Mickael.

Re: SQL UDC based on views

Levi_Turner — Fri, 26 Jan 2018 15:45:08 GMT

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:

Re: SQL UDC based on views

mickael_weqan — Fri, 26 Jan 2018 16:10:48 GMT

Hi Levi,

thanks for your answer,

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

UDC is working well now.