Tableau MarkLogic Data Modelling

Question

I am using Tableau with MarkLogic. I have the following XML Structure

<CustomerInformation CustomerId="1">
        <CustomerBasicInformation>
            <CustomerTitle></CustomerTitle>
            <CustomerFirstName></CustomerFirstName>
            <CustomerMiddleName></CustomerMiddleName>
            <CustomerLastName></CustomerLastName>
        </CustomerBasicInformation>
        <CustomerEmplyomentDetails>
            <CustomerEmployer>
                <EmployerName IsCurrentEmployer=""></EmployerName>
                <CustomerDesignation></CustomerDesignation>
                <EmployerLocation></EmployerLocation>
                <CustomerTenure></CustomerTenure>
            </CustomerEmployer>
        <CustomerEmplyomentDetails>
        <PolcyDetails>
            <Policy PolicyId="">
                <PolicyName></PolicyName>
                <PolicyType></PolicyType>
                <PolicyCategory></PolicyCategory>
                <QuoteNumber></QuoteNumber>
                <PolicyClaimDetails>
                    <PolicyClaim ClaimId="">
                        <PolicyClaimedOn></PolicyClaimedOn>
                        <PolicyClaimType></PolicyClaimType>
                        <PolicyClaimantName></PolicyClaimantName>
                    </PolicyClaim>
                </PolicyClaimDetails>
                <PolicyComplaintDetails>
                    <PolicyComplaint ComplaintId="">
                        <PolicyComplaintStatus></PolicyComplaintStatus>
                        <PolicyComplaintOn></PolicyComplaintOn>
                    </PolicyComplaint>
                </PolicyComplaintDetails>
                <BillingDetails>
                    <Billing BillingId="">
                        <BillingAmount></BillingAmount>
                        <BillingMode></BillingMode>
                    </Billing>
                </BillingDetails>
            </Policy>
            <Policy PolicyId="">
            <PolicyName></PolicyName>
            <PolicyType></PolicyType>
            <PolicyCategory></PolicyCategory>
            <QuoteNumber></QuoteNumber>
            <PolicyClaimDetails>
                <PolicyClaim ClaimId="">
                    <PolicyClaimedOn></PolicyClaimedOn>
                    <PolicyClaimType></PolicyClaimType>
                    <PolicyClaimantName></PolicyClaimantName>
                </PolicyClaim>
            </PolicyClaimDetails>
            <PolicyComplaintDetails>
                <PolicyComplaint ComplaintId="">
                    <PolicyComplaintStatus></PolicyComplaintStatus>
                    <PolicyComplaintOn></PolicyComplaintOn>
                </PolicyComplaint>
            </PolicyComplaintDetails>
            <BillingDetails>
                <Billing BillingId="">
                    <BillingAmount></BillingAmount>
                    <BillingMode></BillingMode>
                </Billing>
            </BillingDetails>
        </Policy>
    </PolcyDetails>
</CustomerInformation>

I have created a view on above structure. Initially I have created a single view for all elements, but on Tableau I got duplicate values as well as Cartesian join result. So to tackle this, I used approach of fragment root. Since there can be multiple PolicyDetails for single customer. I have created fragment root on Policy. Similarly Claims, Complaints, Billing, Quote can be multiple for single policy, I have created fragment root on each one of them.

Now after doing this it resolves the duplicate issue as well as Cartesian join result set. It gives unique set of record for each entities (CustomerInfo, Policy, Claims, Complaints, Quote, Employer, Billing).

However I am not able to relate this entities with each other (as in foreign-primary key).

I have created the following view with element scope and all. I am pasting only Customer and Policy details, if this resolves other entities can be similarly managed

view:create(
  "InsurancePOC",
  "CustomerBasicInfo",
  view:element-view-scope(xs:QName("CustomerInformation")),
  ( 
    view:column("CustomerId", cts:element-attribute-reference(xs:QName("CustomerInformation"), xs:QName("CustomerId"))),
    view:column("PolicyId", cts:element-attribute-reference(xs:QName("Policy"), xs:QName("PolicyId"))), 
    view:column("QuoteNumber", cts:element-attribute-reference(xs:QName("Quote"), xs:QName("QuoteNumber"))),
    view:column("ComplaintId", cts:element-attribute-reference(xs:QName("PolicyComplaint"), xs:QName("ComplaintId"))),
    view:column("BillingId", cts:element-attribute-reference(xs:QName("Billing"), xs:QName("BillingId"))),:)
    view:column("CustomerFirstName", cts:element-reference(xs:QName("CustomerFirstName"))),
    view:column("CustomerLastName", cts:element-reference(xs:QName("CustomerLastName")))                        
  ),
  (),
  () 
),
view:create(
  "InsurancePOC",
  "PolcyInfo",
  view:element-view-scope(xs:QName("Policy")),
  ( 
    view:column("PolicyId", cts:element-attribute-reference(xs:QName("Policy"), xs:QName("PolicyId"))),
    view:column("PolicyName", cts:element-reference(xs:QName("PolicyName"))),
    view:column("PolicyType", cts:element-reference(xs:QName("PolicyType")))                    
  ),
  (),
  () 
)

All pre-requisites like element-range index and all is been done.

I am trying to relate these entities using view:column("PolicyId", cts:element-attribute-reference(xs:QName("Policy"), xs:QName("PolicyId"))) in CustomerBasicInfo view.

If I do so it shows zero results in Tableau or Query console. If I remove it, gives unique record but without any relationship with each other. All I want is to achieve relationship between Policy-Customer

Kindly go through the code snippet, if more clarification required please let me know

Answer 1

The getting of cartesian join results is a known issue with the SQL views driven from Range indexes in MarkLogic, particularly with aggregate docs like above.

The simplest way to solve it for SQL views would be to split your docs into separate Policies, with embedded copies of the customer into. That could mean a fair amount of data duplication if customers often have multiple policies.

You could also consider taking these docs apart, and storing policies and customer details separately, with id refs from policy to customer, so that you can join them together afterwards, in Tableau, or SQL.

MarkLogic 9 comes with a new feature though, that would prevent the need for all this. It is called Template Driven Extraction. It also provides SQL views on data, but works in a different way. It is driven with a match pattern (called the context) that controls the rows in the view. You would use Policy as context in this case. From there you would use relative paths to go up the tree to customer details, and down to get policy details.

TDE templates are installed using tde:template-insert. The documentation of that function shows a simple example of such a TDE:

http://docs.marklogic.com/tde:template-insert

You can also play around with tde:node-data-extract first, to get the hang of it.

HTH!