search

intakedesk / PowerBI-General

Placeholder for issues migrated from Jira
1 stars 0 forks source link

JG: Test Power BI Elasticsearch CData connector in DirectQuery mode #379

Closed jesusitd closed 4 years ago

jesusitd commented 4 years ago

https://www.cdata.com/drivers/elasticsearch/powerbi/

Tested this connector in DirectQuery mode from Power BI Desktop against Elasticsearch cluster and performs awesome.

Can't get it connected from Power BI Service. Requested CData support. Waiting on their response.

jesusitd commented 4 years ago

CData Support replied back with instructions to put the .pqx file in C:\Data Connectors https://www.cdata.com/kb/tech/elasticsearch-powerbi-gateway.rst

image

jesusitd commented 4 years ago

Had to Manually Add the connector online with Manage Gateways, and Done!

image

image

jesusitd commented 4 years ago

Need to install Logstash on the AWS server to perform the data ETL. Working on it

jesusitd commented 4 years ago

AWS Server 18.209.97.85 now has a Service installed with NSSM named LogstashIntakeCases, which triggers the logstash process developed for the Cases Floor Monitor.

The corresponding report relies on CData Power BI / Elastic connector in DirectQuery mode to make this possible (Operations workspace / ELASTIC Cases report) image

jesusitd commented 4 years ago

Test finished.

Will we move this report to production?? @CesarITD

jesusitd commented 4 years ago

CC @CesarITD

Asked some questions to Frank, to see if we can filter by TODAY and THIS WEEK (DirectQuery does not provide enough functionality to do this the specific way we need, without breaking itself into Import mode):

Hi Frank, we are trying to continue reporting using the index we created for the "Cases" sql statement we began with.

At this point we are in need of some way of marking every "case" document with a flag that says "Today", and another flag that says "This week", so we can filter using these 2 attributes in our report.

The 'Today' flag would need to be 1 IF the case timestamp (date_entered) is between Today's 00:00 and 23:59 EST, where as 'This Week' must be 1 between this week's monday, 00:00 to sunday 23:59 EST. The challenge arrives when it's the next day, the Cases that were marked as 'Today' must be now set to 0, and by next week, the same must happen with 'This Week' being set to 0 (basically doing a constant update).

Is there any Elasticsearch native solution we can use to implement this constant update behavior?? (could run on schedule) Or any other ideas??

Is it possible within Elasticsearch/logstash to define an index to be an -aggregation- of another index?????

Let's say we have the intake_cases index that has a case_id, date, agent and case_type. We'd like to create another aggregated index that has date, agent, case_type AND cases count

Thanks!!!!
jesusitd commented 4 years ago

Frank Foti:

For #1 if it is for the reporting in Kibana we could use scripted fields. I have done that on other use cases and works well. https://www.elastic.co/blog/using-painless-kibana-scripted-fields. We could use the similar scripting in a index search outside of kibana if needed.

For #2 I would suggest a scheduled create index to do the aggregation. Is this daily? Weekly Hourly?

I can put together the POC on my side for these. Each would take about 2 hours to put in place unless we hit any issues that I do not anticipate. Once I have the POC of each we should be able to put them both in your environment easily. Let me know if I should proceed and when you heed it done by. Thanks

CC @CesarITD

jesusitd commented 4 years ago

Worked around Today and This Week filters issue using DAX in latest version of ELASTIC Cases.pbix. C:\Users\.......\Intakedesk LLC\Power BI Documentation - Power BI Desktop Files\- Reports Live\Operations

Achieved Display - Agents Packages and Meetings from Elasticsearch with DirectQuery. image

TEST SUCCESSFUL.

DONE.

Can decide to move this to production upon licensing the Connector in Server version. Separate issue.