Updating Qlik Sense Enterprise Bookmark Ownership: A Step-by-Step Guide
As part of a recent migration to a new Multi-Factor Authentication (MFA) provider for our users, we encountered a challenge that many Qlik Sense administrators may face: migrating user-specific data while ensuring continuity in personalized objects, including bookmarks. Here's a detailed look at how we navigated this migration process and handled the transfer of Qlik Sense bookmarks between users.
The Migration Process
We were migrating users to a new MFA provider, a task that necessitated creating new user profiles in Qlik Sense due to username changes. This required us to move all custom objects, including apps and sheets, from the old user profiles to the new ones to ensure a seamless transition. Once we had completed the migration for most objects, a user reached out, wondering where their Qlik Sense bookmarks had gone.
Initial Investigation
To investigate the missing bookmarks, we began by checking the user profile in the Qlik Management Console (QMC). We examined the list of owned objects and confirmed that all the other data, such as apps and sheets, had been successfully transferred to the new user profile. However, the bookmarks were still absent. This led us to explore the technical limitations of Qlik Sense's QMC.
The Challenge: Ownership of Bookmarks
Upon further research, we discovered a key limitation: it isn’t currently possible to change the ownership of bookmarks directly via the QMC. This left us with two main options to update the ownership of the bookmarks:
Use API Calls: The Qlik Sense API allows for programmatically changing ownership by finding the owner of a bookmark and updating it via API calls.
Direct Database Manipulation: Alternatively, we could manipulate the database directly to change the bookmark owner.
After considering the options, we decided to go with direct database manipulation, which offered a straightforward and more immediate solution to the problem. If you choose to follow this method, please do so with caution and according to your review of the associated risks.
Steps for Updating Bookmarks Ownership via Database Manipulation
Given the complexity of directly interacting with the database, we took extra precautions. First, we ensured a full backup of the database, as any modification carries inherent risks. We also took the Qlik Sense services offline during this process to minimize the risk of data corruption or conflicts.
With these safeguards in place, we proceeded with the following steps using pgAdmin 4, the database management tool bundled with Qlik Sense.
Step 1: Identify the Old and New Users
We began by querying the public."Users" table to identify the old and new user IDs. This allowed us to track the owners of the bookmarks in the next step.
Example Query:
SELECT * FROM public."Users" WHERE "UserId" IN ('old_user_id', 'new_user_id');
Step 2: Find Bookmarks Associated with the Old User
Next, we queried the public."AppObjects" table to locate any bookmarks associated with the old user. This step helped ensure that we identified the correct objects before making any changes.
Example Query:
SELECT * FROM public."AppObjects" WHERE "Owner_ID" = 'old_user_id' AND “ObjectType” = ‘bookmark’;
Step 3: Update Ownership in the Database
Once we had confirmed the bookmarks, we used an UPDATE statement to change the owner ID from the old user to the new one. This was the key step in transferring the ownership of the bookmarks.
Example Query:
UPDATE public."AppObjects"
SET " Owner_ID “= 'new_user_id'
WHERE " Owner_ID " = 'old_user_id'
AND “ObjectType” = ‘bookmark’;
Step 4: Verify the Changes
To ensure that the ownership change was successful, we queried the public."AppObjects" table again to verify that the bookmarks now listed the new user as the owner.
Example Query:
SELECT * FROM public."AppObjects" WHERE " Owner_ID " = 'new_user_id' AND “ObjectType” = ‘bookmark’;
Wrapping Up
By following the above steps, we successfully updated the ownership of Qlik Sense bookmarks from the old user to the new one. The process involved querying the Qlik Sense database, identifying the objects, and using SQL to transfer ownership. While it required taking precautions, such as backing up the database and taking services offline, this approach provided a reliable solution to an issue that cannot currently be resolved directly through the QMC.
Direct database manipulation isn't always the preferred route due to potential risks, but in this case, it allowed us to resolve the issue quickly and efficiently. If you face a similar challenge, ensure you have a backup, work in a controlled environment, and carefully execute the necessary queries.
In conclusion, migrating Qlik Sense users and objects can present unexpected challenges, but with careful planning and attention to detail, such as understanding where bookmarks are stored and how ownership is managed, you can ensure a smooth transition for your users.
Need help with Qlik Sense? Visit Qlik Sense or contact us directly at https://www.bizxcel.com/contact to get started.