Load aggregated data in a power BI

[PBILover]

Hi,

I am trying to load a distinct count of ids from Cosmos db by grouping them on a country column . This data should display in a monthly and weekly based and last 13 months data should be used for this aggregation.The datasource contains very huge amount of data.This Data is in Cosmos DB . When i am using the Group by function in a PBI it is taking much time  for 1 months data and not working appropriate.

Is there any way that i can use load only counts

Desired output

Country	May-2020	June2020	July 2020	Aug 2020	And So on
USA	20100	256330	25000	25600
Colombia	25000	341515	215665	1655
Brazil	12215	54564	564963	55983

I tried to use ODBC connector with direct Query mode as listed here https://docs.microsoft.com/en-us/azure/cosmos-db/odbc-driver but i am not able to handle Date operations like load data only for last 13 months as well group by on month  as the column type is in varchar for this connector

[AllisonKennedy]

@PBILover  What does the raw table look like in the database? I think ideally we'd like the data to be unpivoted, so have the data model table look like this, but with all 13 months for each country

Country	Month	Count
USA	May-20	20100
USA	Jun-20	256330
USA	Jul-20	25000
USA	Aug-20	25600
Colombia	May-20	25000
Colombia	Jun-20	341515
Colombia	Jul-20	215665
Colombia	Aug-20	1655
Brazil	May-20	12215
Brazil	Jun-20	54564
Brazil	Jul-20	564963
Brazil	Aug-20	55983

Then pivot the data using a Matrix

[richbenmintz]

Hi @PBILover ,

Are you able to solve this problem with data engineering, ie create an aggregate table in another cloud store like sql azure or azure data lake which refreshes as required and then use PBI to query the pre aggregated data?

[PBILover]

thanks @richbenmintz  , i havent tried this. but will definitely try it .

[lbendlin]

You should be able to use the standard SQL syntax, including casting etc.  Show your query.

[PBILover]

Thanks @lbendlin  I am able to do with hardcode date, my query is  like  below

select attributes_SS,id
from table
Where sentOn >= '2021-01-16'
AND sentOn < '2021-01-19'

but i am unable to write any dynamic code for date calculations,  also not able to use any date functions like getutcdate() function .

[v-shex-msft · Accepted solution]

Hi @PBILover,

I'd like to suggest you enter the advanced query editor and replace these hard code date dynamics based on DateTime functions.

Full Query:

let
    currDate=Date.From(DateTime.LocalNow()),
    t_sql= "select attributes_SS,id from table Where sentOn >= '"&Text.From(Date.AddMonths(currDate,-13))&"' AND sentOn < '"&Text.From(currDate)&"'",
    Source = Sql.Database("server","database",[Query=t_sql])
in
    Source

Notice: t_sql is calculated based on the current system date time, its range will dynamically change if the current date changes.

Regards,

Xiaoxin Sheng

[PBILover]

Thank you very much @v-shex-msft  this syntax works by doing little changes, like instead of using Text.From i used Date.ToText which works fine in my case.

But another problem i am facing here is that this is loading a granular level data in a power BI , and as this dataset is very hug I want to load the count by month (aggregated data) in a power BI. for e.g. select attributes_SS, count(id), month(senton)

                 from table

                 where senton is in last 13 months

Group By  attributes_SS,month(senton)

[v-shex-msft]

HI @PBILover,

I think you can directly do these summaries in your t-sql string, it should more complex to do these on the query table side. 
For the front level of the report view, the power bi chart will auto aggregate the value fields based on your chart axis and legends.

Notice: not all types of SQL functions works when you send from power query data connector additional parameters. 

Regards,

Xiaoxin Sheng

[PBILover]

Thanks for the reply @v-shex-msft  . My dataset is huge dataset and if i load all the data for last 13 months in a power BI then power BI will crash. Even for the increamental dataset it will not work as first time it will load all the data. Thats why i am trying to do the aggregations at a query side. This report is for the management and not for frequent use and so trying to use the direct query mode.

Can you please any other possible solutiond so that i dont have to load all granular level data and will load only aggregated data in a power BI?

Please Note: My actual db is a cosmos DB.

Thank you very much for your support.

[v-shex-msft]

HI @PBILover,

In fact, the cosmos connector also supports this feature.

You can click the 'SQL statement' option and paste the summarize t-sql string in it to enable the custom sql string with your connector, then power bi will try to get data and process the optional sql string to get the summarized results.

Regards,

Xiaoxin Sheng