Performance issue sql and append query

[nirrobi]

Hi all,

1-Is it better to load data using get data or is it prefeered to write sql query in source field?

is there any diffrent at all?

2-I have 2 tables that I want to append together. I load the 2 tables but when append the 2 table it fell like to me that there are duplicate of data in my model (the big table and the other table). should I do sumething diffrent or is it OK?

Many thanks.

Nir.

[spiskai]

Hi Nir. I think I currently have the same question as you had, and don't think you ever got the answer you were looking for. Perhaps you know the answer now.

I work for a Wine and Spirits retailer and we save data it two tables (wine and spirits). Same information (sales, inv, orders, etc.), but the product set in each is different. I'm currently pulling information through 2 sql queries, one wine/one spirits, and appending them in Power BI. 

As far as my data model goes, does appending the two together increase the file size of my data model by 50%? Or is PowerBI not really saving a new copy of the data? Is there a way to delete the spirits dataset post-append, but still be able to refresh the appended table?

Unit sizes are just to convey approx size

Pre-Append

1 table of wine (50units)

1 table of spirits (50units)

Post-Append

1 table of wine and spirits (100units)

1 table of spirits (50units)

Thanks,

Steve

[nirrobi]

Hi,

In my humble opinion you should (that's what I did) do as follow:

1. The best option is to write UNION function in the SQL statement itself (I think that someone in the reply said that the closer you are to the source the better / faster)
2. The 2nd best option is to load the the table you have use the append in PQ but make the 2 original tables to "unload" in the left screen of the PQ.

Hope it help youa little.

Regard,

Nir

[spiskai]

Thanks. That's what I've heard too.

[Seth_C_Bauer]

1. Depends. Typically it is better to only pull the data you plan on using. For example, if I only care about Company A and I can "filter" the source data to 10,000 rows instead of 10 million, then yes write a query. Another reason would be that your source data isn't all in the same table, and you need to write a query to pull the information in.

2. Append is just going to include all rows from both datasets, so if you have the same data in both sets, you'd get "dups"

[nirrobi]

Thanks for your reply!

1:

first sentence:

I indeed have to filter my data cause it load data from 2010 and I need only last 3 years (2013-2015).

I worte query that filter only the relevant data but wonder, is it possible to not use query and only use the amazing GUI that power bi desktop has.

second sentance:

Is it "better" to make the connection between 2 tables through sql query or via manage relation? ( I fimiliar with both method but wonder which is prefeerred?)

When writing query I loose the ability to add information to my model via the power bi desktop gui but only via sql query

2:

I have 2 tables with import data and export data (with the same culomns more or less). I import both tables and then append the 2 tables (so have one with all import and export data) but the original tables is still in my model (duplicate).

Should I have something diffrent?

Many many thanks!

Nir

[Seth_C_Bauer]

If you need the data, or may want to use it for reporting in the model - pull it all. If you don't need it, don't pull it - as your just adding bloat to your model (depending on how much, you could impact model performance).

It's preference, but I like to structure everything in SQL, and only add measures and calc columns in PBI Desktop.

Once you close and load the query, in the data area you can right click the "table name" in the fields section and choose - "Hide in Report View" and it will remove it from the visuals section.

I would approach it like this: if you know that you aren't going to use some of the data, and you can join these two datasets together in SQL, then I would do that prior to bringing the data over. But only you can weigh the pro's/con's based on data set size, production vs. testing, performance.
If none of those are a concern, use whatever tool you are most comfortable with.

[nirrobi]

Thanks a lot for all of your help!

just to make sure, as I don't have much experience with sql, does it better to make using sql one huge table with all of my data or to bring fem tables (approximately 5-6) and combine them using manage relationship in power bi?

Regards. 

Nir. 

[Seth_C_Bauer · Accepted solution]

SQL - Limit the data size (if this is a factor), shape the data (facts/dimensions)

Model - Create relationships between (Facts/Dimensions)

You will eventually want to create relationships in the model.

I have no idea what the tables look like, so I can't comment on how you should pull it in. I would just recommend not pulling in what you don't need. The relationships you build in the desktop are for different purposes than pulling together your data in SQL to put it into the best form (Fact table, dimension tables)

[nirrobi]

Many many thanks. 

I have in my model table that take data from 2 tables in oracle database using sql. 

I think that I can add more tables and relevant data to the sql, is it better in your opinion?

for now I have 3 different sql query that create my model.