Large volume of data

[Anonymous]

Hey Everyone,

 I am new to Power BI and also have little to no experience in syntax or sql language etc. I am testing this platform for a small department within my company and therefore have very little internal resources. I have found some road blocks when dealing with our large amount of company data. I tried creating a canvas with only our 2017-2018 sales information and a refresh/applying a query change takes over an hour because of more than 18 million rows. I have watched hours of youtube videos and scrolled through several pages of forums but they either aren't what I am looking for or the language is too sophisticated for me. At this time, I am focusing on learning two things: 1.) is there a way i can create a canvas that has sustains the millions of records for adhoc reporting where all my fields will already be available depending on the type of request. and 2) I tried learning how to tie tables together and i was getting (blank) Names in my data even though I don't have Blank Names in my data set. Not sure if it has to do with how the tables are connected or what. Looking for any help, on a very basic level, of how I can accomplish these two things. Thank you in advance!

[HotChilli]

It's difficult to give advice without knowing the data source or the complexity of the import or what you are trying to achieve with your visualisations.

Some general tips are - don't import data you don't need, prefer numeric fields to text. Decide what level of aggregation you need.  Nobody needs to see 13 million rows in a table (i hope) 

If you are investigating powerbi, start with simple table imports and visualisations

[Anonymous]

That's a fair statement, thank you for your feedback and suggestions. I am connecting to Hana data model and the "what i am trying to achieve" is the part proving to be the most difficult, because it can be so variable.  Let's just say as an example, You have $10million in sales in 1 year, when you break something down to an individual SKU level of each transacation, you can see how that would add up quickly.  Depending on the request, someone might want to see the SKU's sold per each Vendor, or someone might want to see the SKU's sold per End-User, or someone might want to see the SKU's on a categorical level. Some might just want 12 month data, some might want a trend of the last 5 years. So I would like to have a pbix created, with multiple years of information, down to the SKU level. But that is in a usable format (perhaps in yearly tables, although this still leaves millions of records per table) so that depending on the request, (by vendor/customer/category/etc) I have all the data in the model already, and just just drag the fields I need on to the canvas.  Long story short, if someone asks me to create an adhoc request for data back 5 years, I want something established that wont take an hour to pull in. Does that make a little more sense or paint a better picture of what I am trying to accomplish? Thanks!

[HotChilli]

Well, the first thing you need to decide is what you need powerbi for.  If it's all about ad-hoc investigations, load up a model, sit with some analysts and see what they really want to do

Can I ask why you have to refresh the data to meet report requests? If you're looking at 5 years of data, set your model up, import the data and have it sitting there ready.  Is performance a problem once the model is loaded? 

Perhaps you could show me a sample of the query that you are sending.  You also need to investigate any constraints on your system e.g. PC hardware, network, tuning of any queries (server side and/or powerbi query folding)

[Anonymous]

The time when the refresh becomes difficult is when i refresh it to bring in the current month. For example, I had one created for Jan 2017 - Sep 2018 and when I refreshed to bring in October and November, it took quite a while. I will see if I can come up with a version of my report that is safe to share, but unfortunately since it is sales/customer information for our company, it is confidential.

How about my other question, do you know by chance why when I tie two tables together, I get "blank" results on my graphs even though I dont have blanks in my data? For example (and using the same data test from above), lets say I have a table of 6 customers, and 1 table with all of my sales data. I would like to have a graph on my canvas showing all of our company sales information, but ALSO have a graph on the canvas that is the sales and how it pertains to those 6 customers. When I tie the tables together, I get $1 million in sales for these 6 customers, but then I have a bar on the graph that says $9 million for "blank" to make up the rest of the $10 million.  Even though there are no blank customers on either of my tables. Is that a problem with the directional join? or perhaps I missed a step? Thank you for all your help by the way! You have been very patient and helpful!

[HotChilli]

I think i'd need to see a picture of 'relationship view' to investigate that.  Check that powerbi has the correct 1-m relationship set up, linking the correct fields and make sure they are the same datatype.  Did you set the relationship up yourself or allow autodetect?

[HotChilli]

..and you can also investigate Direct Query with SAP Hana