Hello Folks,
It's been a while most of us started showing Clarity PPM data on Power BI dashboards. It is always a question for me how we can show the live Clarity data if we are on Cloud/SAAS instance. So here is what I achieved so thought of sharing it with community.
Let me explain this with the series of questions that I answered by myself.
- Did anyone tried this? May be or may be not but I couldn't find any after extensive search either in communities nor with Google.
- As clarity is hosted on SAAS, we don't have Database connection so is it really possible to show clarity live data in power bi? Initially the answer is no so we were using direct query for on premise and for on demand instance no way :(
- Is using Odata solves the purpose? - Partially if user is patient enough to see the data updates after a day, but as we all now not many users are having that level of patience ;)
- How about using web services connection in power bi? Not easy to build but I manged to build . But hit with a roadblock of refresh schedule so it is not real time instead we have to wait for refresh schedules in power bi with limitations on number of refreshes based on licensed capacity.
- So give up? No let's keep trying and finally I did figure out a solution, so here it goes
What do we need:
- Obvious Power BI pro license to publish the report.
- SQL Server instance(Not particularly Clarity Database, any sql server instance in your organization)
- A user account to sql server
- Enabling OLEDB extended stored procedure using below commands
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
EXEC sp_serveroption
@server = '<Your Server Name>',
@optname = 'DATA ACCESS',
@optvalue = 'TRUE';
- Power BI gateway (if needed and I think it is needed and most of you would already have this)
What needs to be done:
- Another obvious step, open Power BI Desktop
- Get Data using SQL server
- Provide your server name and database, Select "Direct Query"
- Now here is the Core solution for our approach which you need to paste in "Sql Statement" under Advanced options.
- Utilizing OLEDB procedure to read the SOAP request from Clarity
- Create NSQL for your required data in clarity(Here I am using OOTB Project Status listing(a random pick))
- SQL Server gets SOAP response of NSQL, parses it and returns as table exactly as what we wanted
- Rest depends on your imagination on how you wanted play around with the data with Power BI functions and calculations.
select * from openquery(<YourSQLServerName>,
'
SET FMTONLY OFF;
SET NOCOUNT ON;
DECLARE @XML xml
DECLARE @Result int
DECLARE @obj int,
@requestBody VarChar(4000),
@url VarChar(4000),
@response VarChar(8000),
@requestHeader VarChar(4000),
@status VarChar(8000)
SET @url = ''https://yourinstance.ondemand.ca.com/niku/xog''
SET @requestBody = ''<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:quer="http://www.niku.com/xog/Query">
<soapenv:Header>
<quer:Auth>
<quer:Username>userid</quer:Username>
<quer:Password>iamnottellingpwd</quer:Password>
</quer:Auth>
</soapenv:Header>
<soapenv:Body>
<quer:Query>
<quer:Code>cop.prjStatusReportListing</quer:Code>
</quer:Query>
</soapenv:Body>
</soapenv:Envelope>''
EXEC sp_OACreate ''MSXML2.ServerXMLHttp.3.0'', @obj OUT with result sets None
EXEC sp_OAMethod @obj, ''Open'', NULL, ''POST'', @url, false with result sets None
EXEC sp_OAMethod @obj, ''setRequestHeader'', NULL, ''Content-Type'', ''text/xml'' with result sets None
EXEC sp_OAMethod @obj, ''send'', NULL, @requestBody with result sets None
EXEC sp_OAGetProperty @obj, ''status'', @status OUT with result sets None
--SELECT @status,@response [RESPONSE]
INSERT xmltest ( yourXML )
EXEC @Result = sp_OAGetProperty @Obj, ''responseText''--, @Response OUT with result sets None
select c.query(''declare namespace q="http://www.niku.com/xog/Query";q:project_id'').value(''.'',''varchar(300)'') as ProjectCode,
c.query(''declare namespace q="http://www.niku.com/xog/Query";q:project_name'').value(''.'',''varchar(300)'') as ProjectName,
c.query(''declare namespace q="http://www.niku.com/xog/Query";q:project_manager'').value(''.'',''varchar(300)'') as projectmanager,
c.query(''declare namespace q="http://www.niku.com/xog/Query";q:project_int_id'').value(''.'',''varchar(300)'') as projectintid
from xmltest x
cross apply
x.yourXML.nodes(''declare namespace p="http://schemas.xmlsoap.org/soap/envelope/";declare namespace q="http://www.niku.com/xog/Query" ;p:Envelope/p:Body/q:QueryResult/q:Records/q:Record'') AS T(c)
')
- Please note to change the sql server name, your ondemand instance, your xog credentials who can read nsql xog.
- You can change the above code with additional columns for the same nsql to get overall status indicator and other fields of nsql.
- Build your visuals as needed and publish.
- That's all. We now have Power BI report which can show Live Clarity Data or DW data if required(build nsql with Data Warehouse as source)
My Next target is to integrate this power bi dashboard into clarity using Power BI embed securely(without prompting users for power bi credentials). I didn't find the solution for this approach implemented for clarity in our communities so I am thinking to do more research on it, but if any one of you implemented this already please share.
Thanks for reading this post and I hope it will help the community if anyone is looking for for this solution but sorry if I bored you with the details.
------------------------------
Regards,
Karthik Reddy
------------------------------