"Cannot find either column "DBO" or the user-defined function or aggregate "DBO.XTR_FOLDER_LINEAGE", or the name is ambiguous."

Version 3

    Xtraction support can provide customers with a connector which allows you to report off of the Xtraction database itself.  Data that people typically use this connector for includes but is not limited to: User Sessions, Dashboard and Folder Information, Permissions, Alerts, etc.


    There is a UDF (User Defined Function) that should be present within the Xtraction database.  You would find this UDF in SQL Management Studio by expanding the Xtraction database and navigating to Programmability > Functions > Scalar-valued Functions > "dbo.xtr_folder_lineage."  The purpose of this UDF is to join certain relevant tables (i.e. dbo.Folder & dbo.User), and display folder/user information seamlessly, in a more hierarchical manner.


    If you do not have this UDF created within your Xtraction database, and you are using the default Xtraction connector, you may receive the following error when attempting to report off of Xtraction data:




    Within the data model itself, you will see several references to this UDF.  For example, within the 'Dashboards' view > 'FOLDER' table:




    If you do experience this issue, run the scripts attached to this document against your Xtraction database.  Instructions are included within the scripts themselves.  First, run the script entitled "Xtraction V15.1 Code_Folder Lineage.sql."  Secondly, run the script entitled "Xtraction V15.1 Permissions_Folder Lineage.sql" to set proper execute permissions for your SQL Xtraction user (the account used to report off of the Xtraction database, i.e. the account used for the connection string configured in the Xtraction Settings application).


    Below is an example where I used a Record List component to view dashboard access data.  Notice how we are referencing both user data and folder data.  The 'Dashboard Folder Path' column is a good example of what our UDF is doing.  If you open the data model file using the Data Model Editor, you'll find this field within the 'Dashboard Access' view > 'Dashboard' table.




    NOTE: In Xtraction 2016.2, this UDF is included out of the box.  Upon upgrading to 2016.2, your Xtraction read/write SQL user needs to be given the db_owner database role in User Mappings.  This UDF is then created when clicking 'OK' and exiting the Xtraction Settings application for the first time.  If your Xtraction user does NOT have the db_owner role, you will receive the error message found in the following article: Xtraction Settings error: CREATE FUNCTION permission denied in database