BI on MarkLogic – Microsoft PowerBI

Share on FacebookShare on Google+Email this to someoneTweet about this on TwitterShare on LinkedIn

Sometime, well maybe often, the question of using BI tools on top of MarkLogic comes up. As describe in a earlier post you can use ODBC/SQL with MarkLogic, it however require some preparation first such as creating the proper Range Index and SQL Views.

What if you just want to use the out of box options?

The REST Search API is such out of box feature that you can use as soon as MarkLogic is installed and loaded with some data.

For example if I have loaded a bunch of order documents, in a collection called customerorder, and have them stored as this:

{
"customerOrder": {
"orderNumber": "10104",
"orderDate": "2003-01-31T00:00:00",
"requiredDate": "2003-02-09T00:00:00",
"shippedDate": "2003-02-01T00:00:00",
"status": "Shipped",
"customer": {
"customerNumber": "141",
"customerName": "Euro+ Shopping Channel",
"contactFirstName": "Diego",
"contactLastName": "Freyre"
},
"orderAdress": {
"addressLine1": "C/ Moralzarzal, 86",
"city": "Madrid",
"country": "Spain"
},
"lineItems": {
"lineItem": [
{
"orderLineNumber": "1",
"productCode": "S12_3148",
"productName": "1969 Corvair Monza",
"quantityOrdered": 34,
"priceEach": 175.25,
"lineTotal": 5958.5
},
{
"orderLineNumber": "2",
"productCode": "S50_1514",
"productName": "1962 City of Detroit Streetcar",
"quantityOrdered": 32,
"priceEach": 53.31,
"lineTotal": 1705.92
},
{
"orderLineNumber": "3",
"productCode": "S18_4027",
"productName": "1970 Triumph Spitfire",
"quantityOrdered": 38,
"priceEach": 140.75,
"lineTotal": 5348.5
},
{
"orderLineNumber": "4",
"productCode": "S32_3207",
"productName": "1950's Chicago Surface Lines Streetcar",
"quantityOrdered": 49,
"priceEach": 65.87,
"lineTotal": 3227.63
},
{
"orderLineNumber": "5",
"productCode": "S24_4048",
"productName": "1992 Porsche Cayenne Turbo Silver",
"quantityOrdered": 26,
"priceEach": 112.37,
"lineTotal": 2921.62
},
{
"orderLineNumber": "6",
"productCode": "S24_1444",
"productName": "1970 Dodge Coronet",
"quantityOrdered": 35,
"priceEach": 55.49,
"lineTotal": 1942.15
},
{
"orderLineNumber": "7",
"productCode": "S50_1392",
"productName": "Diamond T620 Semi-Skirted Tanker",
"quantityOrdered": 33,
"priceEach": 112.28,
"lineTotal": 3705.24
},
{
"orderLineNumber": "8",
"productCode": "S18_2238",
"productName": "1998 Chrysler Plymouth Prowler",
"quantityOrdered": 24,
"priceEach": 144.08,
"lineTotal": 3457.92
},
{
"orderLineNumber": "9",
"productCode": "S12_4473",
"productName": "1957 Chevy Pickup",
"quantityOrdered": 41,
"priceEach": 112.58,
"lineTotal": 4615.78
},
{
"orderLineNumber": "10",
"productCode": "S24_2840",
"productName": "1958 Chevy Corvette Limited Edition",
"quantityOrdered": 44,
"priceEach": 39.6,
"lineTotal": 1742.4
},
{
"orderLineNumber": "11",
"productCode": "S32_2509",
"productName": "1954 Greyhound Scenicruiser",
"quantityOrdered": 35,
"priceEach": 47.62,
"lineTotal": 1666.7
},
{
"orderLineNumber": "12",
"productCode": "S18_2319",
"productName": "1964 Mercedec Tour Bus",
"quantityOrdered": 29,
"priceEach": 130.09,
"lineTotal": 3772.61
},
{
"orderLineNumber": "13",
"productCode": "S18_3232",
"productName": "1992 Ferrari 360 Spider red",
"quantityOrdered": 23,
"priceEach": 198.13,
"lineTotal": 4556.99
}
]
},
"orderTotal": "44621.96"
}
}

I can search using a web browser for all orders with the word corvette using the following:

http://localhost:8000/v1/search?database=rfml&collection=customerorder&q=corvette

My result would look something like this:

REST search result

Now, if I wanted use the data for a report I would probably want to receive the full document and not snippets.

I can do that by adding search options that control my output. In order to use those I would need a REST instance that has a dedicated module database, this is easily done by following these instructions.

Once you have a REST instance you can add search options, you can have multiple and control which to use in the search with the options parameters. In order to get the full document back as part of the result you can use the following search options.

<search:options xmlns:search="http://marklogic.com/appservices/search">
<search:transform-results apply="raw">
</search:transform-results>
</search:options>

Save them in a file, for example raw.xml, and install them using curl with the following command.

curl --anyauth --user user:password -X POST -d@'./raw.xml' -i -H "Content-type:application/xml"   http://localhost:8088/v1/config/query/raw

You have to change so the parameters matches your installation.

We can now use the following search, notice that we now is using our new REST instance and also two new parameters options and format.

http://localhost:8088/v1/search?collection=customerorder&q=corvette&options=raw&format=json

This will give us the following result.

rest_call_output

Now, the title of this is BI on MarkLogic using Microsoft PowerBI so lets get to business.

But before we can start we need to make sure that our REST instance supports basic authentication, done through the administrator GUI.

So, lets start by opening Microsoft PowerBI desktop, chose Get Data and there chose Web and click Connect

Get_data

We then add the same url that we used above when getting all documents,http://localhost:8088/v1/search?collection=customerorder&amp;q=corvette&amp;options=raw&amp;format=json, and click Ok

from_web

We will then get a dialog for accessing the data

login

Choose basic and add the username and password for your Marklogic server and click Connect.

basic_login

The search is now executed and we can start working with the result. We need to transform it into something that PowerBI can use.

We do that by first click on List next to Result

search result

Next we click on Into Table

to-table

Using the default settings and click Ok

to-table-default

We can no start expanding our data, by clicking on the right icon with two arrows

start-expanding

Since we are only interested in the content we will only choose that column and also uncheck the Use original name as prefix before clicking Ok

choose-columns1

Now it is just a matter of expanding and choosing columns until there is no more that can be expanded. So my result would be like below, obviously not showing all columns.

All expanded columns

You should also set the data types by clicking on the question mark by the column name.Column data types

Once done click Close & Apply.

Done.

Now you can build your analysis using all functionality. Like my example below.

dashboard

Rather neat!

/Mats

 

 

Share on FacebookShare on Google+Email this to someoneTweet about this on TwitterShare on LinkedIn