Creating PowerBI Visuals and tiles

[varunsingh]

Hi,

I am a new user of PowerBI so please excuse me if you find my question to be very basic.

I am creating various tiles and reports by using fields of same table. The problem i am facing is that the data in the table is pretty large. Since the past couple of days, the tiles are not getting refreshed. When i open the .pbix report on the PC, my system becomes very slow after sometime because of large volume of data in the table. Beacuse of this, i am not able to find what the issue is.

Can you guys help me with this?

And another thing - Suppose i am creating many different visuals portryaing different metrics but all are using same SQL table, showuld i dulpicate the table multiple times in the same powerbi report or should i make a saperate poewerbi report for each visual.

Thanks in advance.

-Varun

[v-qiuyu-msft]

Hi @varunsingh,

1. Since the past couple of days, the tiles are not getting refreshed.

Do you mean the Power BI Service dashboard display stale data? If that is a case, it's due to the Power BI Service itself. See: Power BI Support.

2. And another thing - Suppose i am creating many different visuals portryaing different metrics but all are using same SQL table, showuld i dulpicate the table multiple times in the same powerbi report or should i make a saperate poewerbi report for each visual.

We can create one query to retrieve data from SQL Server database, and create multiple visuals to display this table data, without duplicating query to create another same tables.

3. When i creted the report that time there was less data in the table so it was working fine. Now the data is more in the table and the refresh of the datasets is failing and i am not able to load this much data in my PC as my PC has limited ram.

Do you mean you run the report in Power BI Service or Desktop? If in Power BI Desktop, please only import useful data from SQL Server database. Or please change Import mode to DirectQuery mode suggested by @BhaveshPatel.

Best Regards,
Qiuyun Yu

[varunsingh]

Hi,

Thanks for the reply. 

I created the .pbix report using Import rather than direct query. How to modify the .pbix file to use direct query mode.

Also while using the direct query mode, how to remove duplicates (if any) from the dataset. Because i see "Remove Duplicates' feature working fine with import method but not with direct query method.

Thanks,

Varun

[mpo]

Hi, welcome to the forums!

Things you could try:

 - remove unused columns in the original sql query (not PowerBI query editor)

 - aggregate rows: for example if you always calculate sum of certain columns, try calculating "sum()" with "group by" in sql query.

 - if you only use portion of your data, for example, always filter and show only recent year, do this filtering in sql,

If you post specific details - then you might get more specific suggestions.

Regarding your second question, why do you need to duplicate the table? Just use the same table in each different visual.

[varunsingh]

Hi,

To be more specefic, i have a table which contains more than a billion records. And this table has many columns. Now in powerbi i created the sql connection to this table. I limited the columns which i need for my reports to 4.

Now lets say the columns are timestamp, A, B I have to display reports like trend of A, total of A, trend of B, total of B and few more details like this.

When i creted the report that time there was less data in the table so it was working fine. Now the data is more in the table and the refresh of the datasets is failing and i am not able to load this much data in my PC as my PC has limited ram.

How to solve this?

Thanks,

Varun

[BhaveshPatel]

Hi Varun,

As I suggested, Direct Query would be more appropriate option for your case as you have bottlenecks in terms of limited RAM and there is currently no alternate turnaround for the data compression in powerbi.

Given that you do not require complex and advanced calculations in your data model. 

Is there any specific reasons not choosing direct query option?

[BhaveshPatel]

Hi Varun,

You do not need to duplicate your table or need to create a separate visual for your your Reports. PowerBI is a in memory columnar oriented analytical engine which operates on your machine's RAM(Memory) and it is important to know that large volumes of data can significantly slow down the powerbi. For signifactly better performance, it is required to load only those columns which you would use in your analysis. There are many other considerations and advice as well which you can refer at here.

If you are working with SQL Server, You can use Direct Query Instead which processes your data in near real time and powerbi connects live with SQL server.