Open OOsiemo opened 10 months ago
Hello -
I think you need to disable bulk reader on the connection.
It’s enabled by default in recent versions, however it cannot handle large strings like the XML blocks found in the NP repository.
“ While we believe that most customers will want all their ODBC connections to use this new capability, it is possible to turn off the Bulk Reader feature for a particular connector. To do this, add the parameter useBulkReader with a value of False to the Advanced section of the connector properties window”
Sent from my iPhone
On Sep 29, 2023, at 12:21 PM, OOsiemo @.***> wrote:
When loading the "entities_xml" FROM "nprinting"."public"."report" table, the SQL SELECT statement only loads a maximum of 255 characters instead of the full contents of that XML column. As a result, the subsequent data transformations down the line based on that initial "entities_xml" column fail. For instance, have a look at these two screenshots (where SQL SELECT statement for "entities_xml" only loads a maximum of 255 characters while the actual length of the same "entities_xml" using pgAdmin is 1,782 characters) for the same report [entities_xml column from NP Governance Dashboard]https://user-images.githubusercontent.com/146464686/271660392-2fd25e89-e680-491c-86af-f98b59b44714.PNG [entities_xml column from pgAdmin4]https://user-images.githubusercontent.com/146464686/271660396-8f8b3133-0e52-43df-9b88-285aa291a5b9.PNG
So in a sense, it seems that the SQL SELECT statement treats that column as a normal ASCII with max 255 characters. Is there a way to treat it as an XML construct instead? By the way, I am using the latest version of NPrinting i.e. May 2023 SR1 (v23.20.3)
— Reply to this email directly, view it on GitHubhttps://github.com/eapowertools/NPrintingGovernanceDashboard/issues/11, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AQLC3IEHUMI7L7S6DQGH5DDX43YOTANCNFSM6AAAAAA5MWLXEQ. You are receiving this because you are subscribed to this thread.Message ID: @.***>
The information transmitted by Qlik is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. Qlik's Privacy & Cookie Noticehttps://www.qlik.com/us/legal/privacy-and-cookie-notice describes how we handle personal information
The information transmitted by Qlik is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. Qlik's Privacy & Cookie Noticehttps://www.qlik.com/us/legal/privacy-and-cookie-notice describes how we handle personal information
Hey Jonny,
Thank you for coming back promptly. However, I don't think I managed to get to that bulk reader setting due to the following reasons: My QlikSense environment is QlikSense Enterprise for Windows May 2023 Patch 5. There is no driver for "PostgreSQL" under ODBC connections. However, there is a PostgreSQL, as a standalone, under Data sources. When selecting that standalone PostgreSQL to create a data connection, it comes with similar properties as indicated in your Nprinting.Governance.Dashboard.v3.Installation.Instructions manual. However as I scratched my head to find a setting similar to bulk reader, I came across this link:
. Even after adding that TextAsLongVarchar = 1 setting, it still didnt work. I guess it is due to the fact that the entities_xml column is text:
There is some text in the above link which says: Notice that there still are limitations to this functionality related to the datatype used in the database. Data types like text[] are currently not supported by Simba and they are affected by the 255 characters limitation even when the TextAsLongVarchar parameter is applied.
Please let me know if it works with these settings.
@. @.
@.***
From: OOsiemo @.> Sent: Monday, October 2, 2023 6:58 AM To: eapowertools/NPrintingGovernanceDashboard @.> Cc: Jonathan Poole @.>; Comment @.> Subject: Re: [eapowertools/NPrintingGovernanceDashboard] entities_xml not fully loaded (Issue #11)
Hey Jonny,
Thank you for coming back promptly. However, I don't think I managed to get to that bulk reader setting due to the following reasons: My QlikSense environment is QlikSense Enterprise for Windows May 2023 Patch 5. There is no driver for "PostgreSQL" under ODBC connections. However, there is a PostgreSQL, as a standalone, under Data sources. When selecting that standalone PostgreSQL to create a data connection, it comes with similar properties as indicated in your Nprinting.Governance.Dashboard.v3.Installation.Instructions manual. However as I scratched my head to find a setting similar to bulk reader, I came across this link:
. Even after adding that TextAsLongVarchar = 1 setting, it still didnt work. I guess it is due to the fact that the entities_xml column is text: [entities_xml properties]https://user-images.githubusercontent.com/146464686/271951751-a4015cef-1f9f-4951-8ace-a5cec110508b.PNG
There is some text in the above link which says: Notice that there still are limitations to this functionality related to the datatype used in the database. Data types like text[] are currently not supported by Simba and they are affected by the 255 characters limitation even when the TextAsLongVarchar parameter is applied.
- Reply to this email directly, view it on GitHubhttps://github.com/eapowertools/NPrintingGovernanceDashboard/issues/11#issuecomment-1742805437, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AQLC3IESYUY5GD5SMTGKY5TX5KM4JANCNFSM6AAAAAA5MWLXEQ. You are receiving this because you commented.Message ID: @.**@.>>
The information transmitted by Qlik is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. Qlik's Privacy & Cookie Noticehttps://www.qlik.com/us/legal/privacy-and-cookie-notice describes how we handle personal information
The information transmitted by Qlik is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. Qlik's Privacy & Cookie Noticehttps://www.qlik.com/us/legal/privacy-and-cookie-notice describes how we handle personal information
Hi Jonny,
Unfortunately, I can't see those settings. This is all I am seeing:
Nprinting Governance Dashboard v3 Installation Instructions.pdf
Here are the screenshots. I've also revised the configuration PDF. Consult the screenshots to ensure you have the settings inputted correctly. The PDF is the overall reference guide for setting up the connection and includes a reference to the bulkreader setting
Thank you, thank you, thank you Jonny! It has worked like a gem! However, one more point to bring to your attention. Upon successful data load, the data model has created a synthetic table as a result of two similarly named fiedls (ExecutionStartMinute and ExecutionEndMinute) in two tables (Executions and Times) hav:
I think you can trace the synthetic keys (ExecutionStartMinute and ExecutionEndMinute) to this block of interval match script at Date section:
To resolve the synthetic key, after the interval match, I have issued the DROP Fields immediately after the interval match (at Line 72):
Can you kindly update the .qvf as well to resolve the synthetic table?
The synthetic key is expected in this situation as it's a regular by product of the interval match.
I know the synthetic key is much maligned both for unexpected results and even when its expected, a sign of a general lack of load script hygiene... but in this case it should not impact results.
I've removed it in the next iteration of this code. The entire governance dashboard was substantially enhanced and rebranded as the NPrinting SaaS Readiness Dashboard. The dashboard is available to Qlik employees and partners and can be used to help assess a migration from NPrinting to Qlik Cloud.
If you are a Qlik partner, please feel free to reach out to me or your partner representative at QLIK for a copy and directions.
Thank you -Jonny
From: OOsiemo @.> Sent: Monday, October 2, 2023 9:59 AM To: eapowertools/NPrintingGovernanceDashboard @.> Cc: Jonathan Poole @.>; Comment @.> Subject: Re: [eapowertools/NPrintingGovernanceDashboard] entities_xml not fully loaded (Issue #11)
Thank you, thank you, thank you Jonny! It has worked like a gem! However, one more point to bring to your attention. Upon successful data load, the data model has created a synthetic table as a result of two similarly named fiedls (ExecutionStartMinute and ExecutionEndMinute) in two tables (Executions and Times) hav:
I think you can trace the synthetic keys (ExecutionStartMinute and ExecutionEndMinute) to this block of interval match script at Date section: [image]https://user-images.githubusercontent.com/146464686/271988381-411d62d0-886a-41ee-9ec2-bfe9530324a8.png
To resolve the synthetic key, after the interval match, I have issued the DROP Fields immediately after the interval match (at Line 72): [image]https://user-images.githubusercontent.com/146464686/271990439-68942f0a-8e1c-4fac-8f92-d9d9a0ebe9da.png Can you kindly update the .qvf as well to resolve the synthetic table?
- Reply to this email directly, view it on GitHubhttps://github.com/eapowertools/NPrintingGovernanceDashboard/issues/11#issuecomment-1743069748, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AQLC3IA6KUAGZHFYI2KEF6LX5LCBDAVCNFSM6AAAAAA5MWLXESVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTONBTGA3DSNZUHA. You are receiving this because you commented.Message ID: @.**@.>>
The information transmitted by Qlik is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. Qlik's Privacy & Cookie Noticehttps://www.qlik.com/us/legal/privacy-and-cookie-notice describes how we handle personal information
The information transmitted by Qlik is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. Qlik's Privacy & Cookie Noticehttps://www.qlik.com/us/legal/privacy-and-cookie-notice describes how we handle personal information
When loading the "entities_xml" FROM "nprinting"."public"."report" table, the SQL SELECT statement only loads a maximum of 255 characters instead of the full contents of that XML column. As a result, the subsequent data transformations down the line based on that initial "entities_xml" column fail. For instance, have a look at these two screenshots (where SQL SELECT statement for "entities_xml" only loads a maximum of 255 characters while the actual length of the same "entities_xml" using pgAdmin is 1,782 characters) for the same report
![entities_xml column from pgAdmin4](https://github.com/eapowertools/NPrintingGovernanceDashboard/assets/146464686/8f8b3133-0e52-43df-9b88-285aa291a5b9)
So in a sense, it seems that the SQL SELECT statement treats that column as a normal ASCII with max 255 characters. Is there a way to treat it as an XML construct instead? By the way, I am using the latest version of NPrinting i.e. May 2023 SR1 (v23.20.3)