<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Clement Selvaraj on Medium]]></title>
        <description><![CDATA[Stories by Clement Selvaraj on Medium]]></description>
        <link>https://medium.com/@clementselvaraj?source=rss-19c61aa1291c------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*abKuk36--hJbVayE.</url>
            <title>Stories by Clement Selvaraj on Medium</title>
            <link>https://medium.com/@clementselvaraj?source=rss-19c61aa1291c------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 16:23:17 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@clementselvaraj/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Build insight apps using Microsoft PowerBI datapool]]></title>
            <link>https://medium.com/@clementselvaraj/build-insight-apps-using-microsoft-powerbi-datapool-e8d5db028906?source=rss-19c61aa1291c------2</link>
            <guid isPermaLink="false">https://medium.com/p/e8d5db028906</guid>
            <category><![CDATA[microsoft]]></category>
            <dc:creator><![CDATA[Clement Selvaraj]]></dc:creator>
            <pubDate>Fri, 10 Aug 2018 10:41:55 GMT</pubDate>
            <atom:updated>2018-08-10T10:41:55.811Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Scenario</strong>: You have a customer who uses <strong><em>CRM Dynamics</em></strong> for <strong><em>sales and service </em></strong>apps but uses <strong><em>Northwind </em></strong>for taking orders— let’s assume Northwind is a software vendor who provides a SaaS solution for <strong><em>Order taking</em></strong><em>. </em>Northwind provides OData services to get a list of <a href="http://services.odata.org/V4/Northwind/Northwind.svc/Orders">Orders</a> and a list of <a href="http://services.odata.org/V4/Northwind/Northwind.svc/Order_Details">OrderDetails</a>. Customer wants to create historical reports mashing data from all three solutions.</p><p>A sales solution typically has business entities such as <strong>Products</strong>, <strong>Customers</strong>, <strong>Leads</strong>. A service solution has business entities such as <strong>KnowledgeArticle, Sessions</strong>. An order taking solution has business entities such as <strong>Order </strong>and <strong>OrderDetails</strong>.</p><p>Not to forget that some <em>master data entities </em>like <strong>Products </strong>and <strong>Customers </strong>are the same business entities used by all three solutions.</p><p><a href="https://powerbi.microsoft.com/en-us/cds-analytics/">CDS for Analytics | Microsoft Power BI</a></p><p>For those from the <strong><em>CRM Dynamics </em></strong>world, the good news is that PBI authoring experience on powerbi.com, gives an out-of-the-box connector that can Getdata from your CRM org database into <em>datapool</em>.</p><p>A <strong>datapool </strong>is a cheap store that should come with your power BI license. A PBI Premium license is planned to offer 100 TB space (that’s a lot of space !!) of per node and a PBI Pro license to offer Per user quota.</p><p>A <em>datapool </em>is very different from the usually talked about <em>datalake </em>in the sense that a <em>datapool</em></p><ol><li>is <strong>s<em>chematized </em></strong>with entities and relationship between entities</li><li>allows you to store all your structured and unstructured data at <strong><em>any scale</em></strong></li></ol><p>PBI authoring experience on powerbi.com also provides other connectors like the one for <strong>Azure Blob Store</strong>, <strong>OData</strong> etc…</p><p>The key takeaway with Datapool as against SQL DW is that, as a customer, you can <strong>bring your own storage</strong> and point the datapool to use that storage.</p><p>It does transform your warehouse system from an <strong><em>ETL </em></strong>(Extract first, Transform next and Load at last) to <strong><em>ELT </em></strong>(Extract first, Load next and Transform as required) at that very instant. Which means that you can bring both kinds of data.</p><ol><li>Transformed to a structure defined in the datapool</li><li>Yet to be transformed (Raw) data</li></ol><p>Many prefer unaltered data in the datapool for reference if there are any problems that need troubleshooting.</p><p>For our example, let’s use the connectors: CDS for App, OData and Azure Blob Store to create a datapool.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/584/1*bIwhde8B2jVEsfMJsiT2OA.png" /></figure><ol><li>Orders, OrderDetails and Customers uses <strong>OData connector</strong></li><li>KnowledgeArticle uses <strong>CDS for Apps connector</strong></li><li>Session uses <strong>Azure Blob Store connector</strong></li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JPN0a0KJRpruLY0f9oMIdw.png" /></figure><p>Just before finishing, the step of creating entities there are 2 options</p><ol><li>to create a custom entity</li><li>to map to an existing entity</li></ol><p>The existing entities are listed on <a href="https://github.com/Microsoft/CDM/tree/master/schemaDocuments#directory-of-cdm-entities">github</a>.</p><p>In the example, mapped to existing entities like KnowledgeArticle and Account. The rest are custom entities.</p><p>The definition of entities on github should help ISVs (Independent Software Vendors) to build apps based on a CDS for Apps data model and ask their customers to write connectors/templates to map to the entities.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/489/1*XmU4pM0Ik3pgqcVOwrblTg.png" /></figure><p>If ISVs write apps around CRM applications as microservices, they will run into choices of having to build a connector or a template to Getdata from their microservices into the datapool.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/898/1*G1_2MSCrnbNy8EeDRNkdJA.png" /></figure><p>Once data is available in the datapool, use the PBI desktop option to build <strong>insight apps </strong>using PBI reports/dashboards connecting to the datapool.</p><p>In addition, bringing a compute (like Spark via <a href="https://azure.microsoft.com/en-in/services/databricks/">Azuredatabricks</a>) to connect to the datapool allows you to do the Transform or even schema on load while torturing data for <strong>intelligence apps</strong>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e8d5db028906" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Querying json in python using Spark SQL]]></title>
            <link>https://medium.com/@clementselvaraj/https-medium-com-querying-json-in-python-using-spark-sql-a08761946dd2?source=rss-19c61aa1291c------2</link>
            <guid isPermaLink="false">https://medium.com/p/a08761946dd2</guid>
            <category><![CDATA[spark-sql-json]]></category>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[json]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[spark-sql]]></category>
            <dc:creator><![CDATA[Clement Selvaraj]]></dc:creator>
            <pubDate>Mon, 05 Mar 2018 08:21:56 GMT</pubDate>
            <atom:updated>2018-03-06T03:13:20.700Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Querying JSON in python using Spark SQL</strong></h3><p>Microsoft’s <a href="http://videoindexer.ai/">Videoindexer.ai</a> and <a href="https://videobreakdown.portal.azure-api.net/">Videobreakdown API</a> can be used to index video and audio files. Once the video has been processed, the <a href="https://videobreakdown.portal.azure-api.net/docs/services/582074fb0dc56116504aed75/operations/5a37a5a80dc5610d2cc48219?">Get Breakdown API</a> returns a complex json. And when one looks for examples on <strong><em>Spark processing </em></strong>this complex jsons and turning them to csv files, there are a couple of good reads on <a href="https://databricks.com/blog/2015/02/02/an-introduction-to-json-support-in-spark-sql.html">databricks site</a>, <a href="http://blog.antlypls.com/blog/2016/01/30/processing-json-data-with-sparksql/">antlypls</a> and <a href="https://spark.apache.org/docs/latest/sql-programming-guide.html#json-datasets">Spark site</a>.</p><p>Below was the json schema of the dataset returned by the <a href="https://videobreakdown.portal.azure-api.net/docs/services/582074fb0dc56116504aed75/operations/5a37a5a80dc5610d2cc48219?">VideoBreakdown request API</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/649/1*elD7Fj_CxSQjVm7nVkYPoA.png" /></figure><p><strong><em>Goal </em></strong>was to extract <em>topics.name, topics.rank, breakdowns.id </em>into the following csv format. Rank is a measure of confidence relative to other topics in that video. One can use it to order the topics by importance.</p><pre><strong># Expected csv format</strong><br>+-----------+------------+----+<br>|breakdownId|       topic|rank|<br>+-----------+------------+----+<br>| ea9279befe| credit card| 4.0|<br>| ea9279befe|pay interest| 4.0|<br>| ea9279befe|      verify| 1.0|<br>+-----------+------------+----+</pre><p>As we read from the json schema above,topics is an array within the struct insights within an array of breakdowns containing not only structs but also other fields</p><p>Get a good grip of the following .selectExpr, .withColumn and .selectfrom from the public documentation available and try the following.</p><pre><strong># Get SQL Context</strong><br>sqlContext = SQLContext(sc)</pre><pre><strong># Read all jsons from the folder mounted on databricks</strong><br>breakDown = sqlContext.read.json(“/mnt/media-breakdowns/*.json”)</pre><pre><strong># Select the fields containing data and set context</strong><br>SDF = sqlContext.sql(“SELECT breakdowns[0].id as breakdownId, \ breakdowns[0].insights.topics as topics FROM breakDown”)</pre><pre><strong># Explode topics</strong><br>topicsSDF1 = SDF.selectExpr(“explode(topics) AS \ structCol”).selectExpr(“structCol.*”)</pre><pre><strong># Display results</strong><br>topicsSDF1.show()<br>+---+------------+----+------------+--------------+<br>| id|        name|rank|        stem|         words|<br>+---+------------+----+------------+--------------+<br>|  0| credit card| 4.0| credit card| [credit card]|<br>|  1|pay interest| 4.0|pay interest|[pay interest]|<br>|  2|      verify| 1.0|      verify|      [verify]|<br>+---+------------+----+------------+--------------+</pre><p>The above operation using selectExpr : explodes topics but loses breakdownId and the below operation usingwithColumn : holds breakdownId but does not explode topics as individual rows.</p><p>And one cannot join the above and below dataframes as there is no key that can be joined on.</p><pre>topicsSDF2 = SDF.withColumn(“topic”, explode(SDF.topics))</pre><pre><strong># Display results</strong><br>topicsSDF2.show()<br>+-----------+--------------------+--------------------+<br>|breakdownId|              topics|               topic|<br>+-----------+--------------------+--------------------+<br>| ea9279befe|[[0,credit card,4...|[0,credit card,4....|<br>| ea9279befe|[[0,credit card,4...|[1,pay interest,4...|<br>| ea9279befe|[[0,credit card,4...|[2,verify,1.0,ver...|<br>+-----------+--------------------+--------------------+</pre><p>Instead, one has to use all the three operations select, selectExpr and explode together in order to do both the following</p><p><em>#1 — hold the </em><em>breakdownId</em></p><p><em>#2 — until </em><em>topics are exploded as individual rows.</em></p><pre><strong># Explode topics</strong><br>topicsSDF3 = SDF.select(&#39;breakdownId&#39;, &#39;topics&#39;). \<br>              selectExpr(&quot;breakdownId as breakdownId&quot;, \        &quot;explode(topics) AS topicsCol&quot;). \<br>              selectExpr(&quot;breakdownId as breakdownId&quot;, \ &quot;topicsCol.name as topic&quot;, &quot;topicsCol.rank as rank&quot;)</pre><pre><strong># Display results</strong><br>topicsSDF3.show()<br>+-----------+------------+----+<br>|breakdownId|       topic|rank|<br>+-----------+------------+----+<br>| ea9279befe| credit card| 4.0|<br>| ea9279befe|pay interest| 4.0|<br>| ea9279befe|      verify| 1.0|<br>+-----------+------------+----+</pre><p>This dataframe can then be used to do aggregates by topic and surface that into a Microsoft’s Power BI dashboard like the one below. The larger the size of text, the greater the importance.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/510/1*ab8K71ASYCCArUvE5fQjNQ.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a08761946dd2" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>