cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBILover
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
AllisonKennedy
Super User
Super User

@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


Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

richbenmintz
Solution Sage
Solution Sage

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?



I hope this helps,
Richard

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

Proud to be a Super User!


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

 

lbendlin
Super User
Super User

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.

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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors