Automatic assigning/removing User and Document Calc in QMC

    Hello,

     

    Tired of manually mapping users and assigning document calcs? Try this:

     

    Introduction:

     

    I wanna share my thoughts on creating users and assigning document calcs automatically. I am working on a BI project where we daily have new users who should have access to their own document in Qlikview.

     

    Setup:

     

    We have made a Qlikview solution where we create a masterdocument, which contains all my data for all users. The document is Incremental loaded every night.

    Next step is distribute the masterdocument out to every user, so they have their own document to access with their own related data based on a UserID. The document is accessed on a website using Webticket to authenticate the user.

     

    Reason:

    I was getting tired of creating and mapping the users manually (maybe there is a smarter way, please do tell if so), so I started reading blogs/documents/community posts about Qlikview API on how to dealing with this issue.

     

    I found alot of examples, but a lot of them with errors in it, or not suitable for my solution.

     

    Since I work mostly with BI and we have a large BI setup, my primary thoughts were to build an automatic SQL table with all my users and reportnames due to i always has this information in my BI backend.

     

    Therefore i created a table containing all information about my users and which documents they should be able to access.

    As a sidenote I also created a BOOL/BIT field called IsActive, so that i could delete the calc and username from the report if it was inactive.

     

    UserNameDocumentNameIsActive
    ADocumentA.qvw1
    BDocumentB.qvw1
    CDocumentC.qvw0

     

    Next step was to code a program so it could automatically assign a user to the document and assign a Document CAL.

     

    Code walkthrough:

    This is only the code for creating and deleting - If you want to connect to the API there is some settings that needs to be done first. If you have any troubles in this, you can contact me, and I will try to help you

     

    // I have downloaded a (dapper) to create this class.

    class UserReport

        {

            public string UserName { get; set; }

            public string DocumentName { get; set; }

            public bool IsActive { get; set; }

        }

        class Program

     

        {

    //I have downloaded something called Nlog to log when i create and delete a user - Hereby i get the information when the user/calc was assigned - I use it to report to Qlikview how many calc's we have used.

              static Logger logger = LogManager.GetCurrentClassLogger();

     

             static void Main(string[] args)

            {

            

                //List();

                //CalInfo();

               //ListDMSUsers();

                //RemoveDMS();

                AddCAL();

                Console.ReadKey();

            }

     

    public static void AddCAL()

            {

                try

                {

                    // Initiate backend client

                    var backendClient = new QMSClient();

     

                    //retrieve a time limited service key

                    ServiceKeyClientMessageInspector.ServiceKey = backendClient.GetTimeLimitedServiceKey();

     

                   //clear the QMS's QVS object cache from user document listings - so that the any new user documents can be discovered

                    backendClient.ClearQVSCache(QVSCacheObjects.All);

                   // get a list of QVS services

                    List<ServiceInfo> qvsServices = backendClient.GetServices(ServiceTypes.QlikViewServer);

     

                    // get a list of all documents on the server

                    List<DocumentNode> userDocuments = backendClient.GetUserDocuments(qvsServices[0].ID);

                  

       // Connect to my SQL table - Reference to my class UserReport - Adding my usernames,documentnames and isactive info to a list

                   string connectionString = "Data Source=XXXXXXX;Initial Catalog=XXXXXX;User Id=XXXXXX;Password=XXXXXX;";

                    List<UserReport> userReports;

                    using (var Sqlconnection =new SqlConnection(connectionString))

                    {

                        userReports = Sqlconnection.Query<UserReport>("SELECT UserName, DocumentName, IsActive FROM                     usernames").ToList();

                    }

                    var documentInfos = userDocuments

                        .Select(x =>

                            new {

                             //Set all to lower

    UserReport = userReports.FirstOrDefault(y => y.DocumentName.Equals(x.Name,StringComparison.InvariantCultureIgnoreCase)),

                                Document = x,

                                //Retrieve documents metadata

                                MetaData = backendClient.GetDocumentMetaData(x, DocumentMetaDataScope.Licensing)

                        })

    //Making sure of that i only match and check by reports from the server with the same name as in my table

                        .Where(x => x.UserReport != null)

                        .ToList();

     

    // Assign 1 document calc to a document - Makeing sure that my IsActive field is 1 = true and that the document has no calcs allocated

                    foreach (var documentInfo in documentInfos.Where(x => x.UserReport.IsActive && x.MetaData.Licensing.CALsAllocated == 0))

                    {

                        // Setting calcs allocated to the document to 1

                        documentInfo.MetaData.Licensing.CALsAllocated = 1;

                        //Adding the username to the report - Using GetNamedCal from a Method (see buttom of code)

                        documentInfo.MetaData.Licensing.AssignedCALs.Add(GetNamedCal(documentInfo.UserReport.UserName));

     

                         //Creating the log file using Nlog - Also writing it out to console

                        var log = documentInfo.UserReport.UserName + " added to document - " + documentInfo.UserReport.DocumentName;

                        logger.Info(log);

                        Console.WriteLine(log);

     

                        backendClient.SaveDocumentMetaData(documentInfo.MetaData);

                    }

     

    // Remove document calc and user - Makeing sure of that my IsActive is 0 = false and that the document has a calc assigned

                    foreach (var documentInfo in documentInfos.Where(x => !x.UserReport.IsActive && x.MetaData.Licensing.CALsAllocated > 0))

                    {

                        var cal = documentInfo.MetaData.Licensing.AssignedCALs.First(x => x.UserName.Equals(documentInfo.UserReport.UserName, StringComparison.InvariantCultureIgnoreCase));

                        // Remove the username

                        documentInfo.MetaData.Licensing.AssignedCALs.Remove(cal);

                        //Set the allocated calc to 0

                        documentInfo.MetaData.Licensing.CALsAllocated = 0;

     

                        //Writing to log again

                        var log = documentInfo.UserReport.UserName + " removed from document - " + documentInfo.UserReport.DocumentName;

                        logger.Info(log);

                        Console.WriteLine(log);

     

                       //Save all the metadata back to the documents so they are updated in QMC

                        backendClient.SaveDocumentMetaData(documentInfo.MetaData);

                    }

     

    //Clearing all cache so that I make sure that all my fields are updated (If this is not cleared, you can have trouble in seeing how many calcs there are in use)

                    if(documentInfos.Any())

                        backendClient.ClearQVSCache(QVSCacheObjects.All);

                }

                //Retrieving any erros there might be

                catch (System.Exception ex)

                {

                    Console.WriteLine(ex.ToString());

                }

                Console.WriteLine("Done!");

            }

     

            //Creating a method for assigning a username

            private static AssignedNamedCAL GetNamedCal(string username)

            {

                var namedCal = new AssignedNamedCAL();

                namedCal.UserName = username.ToLower();

                return namedCal;

            }

     

    Please let me know if you have any ideas or thoughts on how you would do it

    //Thomas