Search code examples
powerbimarklogicmarklogic-10

All row data is not getting reflected in Power BI from Marklogic

I have a csv file with 3 rows of data which is stored in Marklogic and available in collections. I am trying to access the content of the csv file from Power BI. But from Power BI I can only access 1 row of data at one time whereas requirement is to see all rows of data.

Here are the details of the process I followed to achieve the functionality:

  1. Used Marklogic ODBC driver(64bit) to connect Marklogic SQL from Power BI
  2. Created template(used xquery) to implement the table structure in Marklogic
  3. Used mlcp to import the csv file in the marklogic database.

Here is the template I used:

xquery version "1.0-ml";
import module namespace tde = "http://marklogic.com/xdmp/tde" at "/MarkLogic/tde.xqy";

if (xdmp:database-name(xdmp:database()) = "Documents")  then        
    let $shipment-CBE:=
    <template xmlns="http://marklogic.com/xdmp/tde">
     <context>/shipment</context>
       <collections>
         <collections-and>
           <collection>PowerBI</collection>
           <collection>Shipment</collection>
         </collections-and>
       </collections>
       <rows>
         <row>
           <schema-name>Shipment</schema-name>
           <view-name>CBE2</view-name>
           <columns>
             <column>
               <name>DocType</name>
               <scalar-type>string</scalar-type>
               <val>DocType</val>
             </column>
             <column>
               <name>User</name>
               <scalar-type>string</scalar-type>
               <val>User</val>
            </column>
            <column>
          <name>ShipmentRef</name>
          <scalar-type>string</scalar-type>
          <val>ShipmentRef</val>
        </column>
        <column>
          <name>Transmode</name>
          <scalar-type>string</scalar-type>
          <val>Trans_mode</val>
        </column>
        <column>
          <name>Packagetype</name>
          <scalar-type>string</scalar-type>
          <val>Package_type</val>
        </column>
        <column>
          <name>Customer_Party_Name</name>
          <scalar-type>string</scalar-type>
          <val>Customer_Party_Name</val>
        </column>
       </columns>
    </row>
      </rows>
    </template>

    return ( 
      tde:template-insert("/powerbi/shipment-CBE2.xml",$shipment-CBE),
      "shipment-CBE.xml OK"
    )
else ("Please select the 'Documents' database.")

Here is the import command(saved as shipment.txt) used to import the csv file:

IMPORT
-input_file_path
../data/Shipment-CBE.csv
-input_file_type
delimited_text
-delimited_root_name
shipment
-username
admin
-password
*****
-host
owc-db01.owc.com
-port
8000
-output_uri_prefix
/powerbi/shipment/
-output_collections
PowerBI,Shipment
-uri_id
"DocType"

Running this MLCP command to execute the import command -

mlcp.bat -options_file shipment.txt

Solution

  • The issue appears to be that you are importing each of the 3 CSV rows and inserting with the same URI of /powerbi/shipment/Shipment

    This is because you have configured the -uri_id to be the "DocType" column, and each of your rows have the same value: Shipment.

    https://docs.marklogic.com/guide/mlcp/import#id_65814

    Optionally, override the default document URI by setting -uri_id to the name of the element from which to derive the document URI.

    You could instead configure it to use the User column, so that the docs would be inserted with unique URIs:

    • /powerbi/shipment/j_henderson
    • /powerbi/shipment/c_saunders
    • /powerbi/shipment/a_gatfield