Good habits or organizing queries?

[Henceman]

As I have created several queries already, I am familiar with the process, but Im unsure if creation logic is acceptable if I move on to larger models with several dependant queries and I dont want to start with a wrong foot.


Scenario:

One datasource (table)->output to 5 different pivots showing different figures

1. Create main query from this table (load it only to datamodel as a connection)
2. Create another query, referencing the main query, removing columns, doing calculations, load this query as pivot1
3. Create another query, referencing the main query, removing columns, doing calculations, load this query as pivot2
4. Create another query, referencing the main query, removing columns, doing calculations, load this query as pivot3
5. Create another query, referencing the main query, removing columns, doing calculations, load this query as pivot4
6. Create another query, referencing the main query, removing columns, doing calculations, load this query as pivot5

Probably most effective would be, if first pivot gets the data from main query, but rest of the tables depend on the data from the 1st pivot, however, each pivot needs different columns from dataset to be useful, so this cant work.

 

[peter789]

If you have loaded your query to the datamodel I can see no advantage in creating a set of sub queries. Use the datamodel as the source for all your pivot tables and charts adding measures to carry out the calculations you require. This is possibly best practice.
However if you need to keep your file as small as possible, with the downside of losing the capabilities of Powerpivot, then you can certainly use the sub query technique you are proposing. However, there is no need to load to the datamodel (it will increase your file size by at perhaps a factor of 3). Create your individual queries with the calculations you need in added columns, saved as connection only. Then create your pivot tables by selecting external data source.
Peter

 

[Henceman]

Good points, and I presume, if some of the pivots can use the original data without changes, i can set the source data directly as the table, not the query, to save on resources as getting the data through query just slows things down?