cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

Load aggregated data in a power BI

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

CountryMay-2020June2020July 2020Aug 2020And So on
USA201002563302500025600 
Colombia250003415152156651655 
Brazil122155456456496355983 

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

1 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.

3.png

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

10 REPLIES 10
Super User III
Super User III

@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


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Super User I
Super User I

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

Super User III
Super User III

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

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 .

Hi @PBILover,

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

3.png

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

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)

 

 

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

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.

6.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors