Home

Data Model Newbie

%3CLINGO-SUB%20id%3D%22lingo-sub-389379%22%20slang%3D%22en-US%22%3EData%20Model%20Newbie%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389379%22%20slang%3D%22en-US%22%3E%3CP%3EThree%20main%20questions%20(each%20identified%20below%20for%20easy%20reference%20back)...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBackground%3A%20While%20well%20versed%20in%20Excel%2C%20pivots%2C%20and%20fairly%20complex%20matters%20in%20that%20realm%2C%20I've%20never%20used%20Excel%20as%20a%20sql%20tool.%26nbsp%3B%20In%20just%20having%20recently%20created%20a%20new%20pivot%2C%20I%20was%20offered%20the%20option%20of%20including%20it%20in%20a%20data%20model.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQUESTION%201%3C%2FP%3E%3CP%3EAm%20I%20generally%20correct%20in%20my%20understanding%20that%20if%20I%20have%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20essentially%20a%20list%20of%20base%20transaction%20'units'%20(the%20essential%20elements%20off%20of%20which%20I%20need%20to%20run%20numerous%20calculations%2C%20including%20calculations%20of%20interim%20calculations)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethen%20current%20to%20reduce%20file%20size%20one%20of%20my%20best%20bets%20is%20to%20use%20this%20in%20a%20'data%20model'%20and%20use%20query%20language%20to%20run%20numbers%20off%20of%20there%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQUESTION%202%3C%2FP%3E%3CP%3EIf%20I'm%20correct%20in%20the%20above%2C%20is%20it%20as%20simple%20as%20creating%20that%20list%20of%20base%20elements%2C%20telling%20Excel%20%22treat%20this%20as%20the%20data%22%2C%20and%20then%20using%20SQL%20language%20to%20ping%20against%20that%20data%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20newbie%20so%20apologies%20if%20the%20above%20are%20obvious.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQUESTION%203%3C%2FP%3E%3CP%3EIt's%20been%20a%20long%20while%20since%20I%20played%20with%20SQL%20language%2C%20but%20am%20I%20correct%20that%20if%20my%20data%20looks%20something%20like%20the%20attached%2C%20then%20I%20could%20do%20any%20number%20of%20the%20following%20types%20of%20calculations%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20for%20all%20the%20Large%20agencies%20that%20joined%20in%20the%20May-'19%20timeframe%2C%20aggregate%20the%20amount%20of%20professionals%20that%20were%20in%20those%20agencies%20and%20multiply%20them%20by%205%20(which%20means%20that%20first%20the%20calculation%20has%20to%20be%20done%20to%20say%20%22take%20the%20ProsPerAgency%20number%20in%20May-19%2C%20for%20large%20agencies...multiply%20that%20number%20against%20the%20number%20of%20agencies%20that%20joined%20in%20May-19...then%20multiply%20that%20result%20by%205%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThese%20are%20just%20calculations%20that%20represent%20the%20kind%20of%20layered%20calculations%20(and%20some%20of%20the%20calculations%20would%20have%203-5%20interim%20calculations%20involved)%20that%20I'd%20be%20looking%20at%20doing.%26nbsp%3B%20I%20need%20to%20refresh%20my%20understanding%20of%20general%20approach%20that%20SQL%20and%20Excel%20data%20models%20allows%20for%20this.%26nbsp%3B%20I%20know%20how%20to%20do%20it%20with%20a%20series%20of%20pivot%20tables%2C%20but%20that%20doesn't%20seem%20efficient.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20obviously%20a%20fraction%20of%20the%20data%2C%20so%20even%20though%20something%20may%20appear%20only%20once%20here%2C%20in%20the%20full%20dataset%2C%20the%20aggregation%20would%20actually%20yield%20math%20that%20I%20can't%20do%20on%20a%20calculator%20(which%20for%20this%20data%20extract%20I%20can%20do).%26nbsp%3B%20Don't%20anyone%20thinking%20I'm%20trying%20to%20run%20a%20data%20model%20on%2050%20lines%20of%20data.%26nbsp%3B%26nbsp%3B%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20helping%20me%20get%20back%20on%20my%20feet%20with%20SQL%20and%20Excel%20modeling.%26nbsp%3B%20As%20I%20read%20about%20it%2C%20the%20potential%20seems%20there%20to%20transform%20the%20way%20I%20do%20complex%20calculations.%26nbsp%3B%20Just%20need%20to%20understand%20how%20the%20data%20itself%20needs%20to%20be%20structured%20in%20Excel%2C%20and%20then%20a%20quick%20few%20pointers%20on%20how%20query%20language%20handles%20the%20kinds%20of%20things%20I'm%20looking%20to%20calculate.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-389379%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETips%20and%20Tricks%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389447%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Model%20Newbie%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389447%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F299524%22%20target%3D%22_blank%22%3E%40txrussianguy%3C%2FA%3E%26nbsp%3B%2C%20yes%2C%20if%20you%20uncheck%20this%20default%20setting%20file%20size%20could%20be%20reduced%20significantly%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389436%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Model%20Newbie%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389436%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3Ethanks%20very%20much.%26nbsp%3B%20I%20never%20save%20cache%20on%20pivots%2C%20and%20figured%20that%20this%20might%20be%20my%20easier%20near-term%20solution.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389418%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Model%20Newbie%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389418%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F299524%22%20target%3D%22_blank%22%3E%40txrussianguy%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20Excel%20is%20not%20SQL%20tool%2C%20you%20may%20use%20SQL%20query%20in%20connectors%20to%20SQL%20databases.%20In%20Excel%20to%20transform%20data%20is%20used%20Power%20Query%2C%20it%20has%20its%20own%20M-script%20language%20and%20rich%20user%20interface.%20I%20many%20cases%20only%20UI%20is%20enough%20to%20transform%20data%2C%20in%20particular%20transformations%20as%20you%20say%20could%20be%20done%20within%20it.%20Result%20is%20returned%20back%20to%20Excel%20sheet%20as%20Excel%20table.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EData%20model%20(Power%20Pivot%20and%20DAX%20language)%20if%20you'd%20like%20to%20add%20any%20measures%20and%20return%20result%20back%20as%20PivotTable%20(however%2C%20could%20be%20also%20Excel%20table).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20sure%20about%20file%20size%2C%20don't%20think%20you%20reduce%20it%20compare%20with%20PivotTables%20only%2C%20especially%20if%20you%20don't%20save%20PivotTable%20cache%20within%20the%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
txrussianguy
Contributor

Three main questions (each identified below for easy reference back)...

 

Background: While well versed in Excel, pivots, and fairly complex matters in that realm, I've never used Excel as a sql tool.  In just having recently created a new pivot, I was offered the option of including it in a data model.

 

QUESTION 1

Am I generally correct in my understanding that if I have the following:

 

- essentially a list of base transaction 'units' (the essential elements off of which I need to run numerous calculations, including calculations of interim calculations)

 

then current to reduce file size one of my best bets is to use this in a 'data model' and use query language to run numbers off of there?

 

QUESTION 2

If I'm correct in the above, is it as simple as creating that list of base elements, telling Excel "treat this as the data", and then using SQL language to ping against that data?

 

Again, newbie so apologies if the above are obvious.

 

QUESTION 3

It's been a long while since I played with SQL language, but am I correct that if my data looks something like the attached, then I could do any number of the following types of calculations:

 

- for all the Large agencies that joined in the May-'19 timeframe, aggregate the amount of professionals that were in those agencies and multiply them by 5 (which means that first the calculation has to be done to say "take the ProsPerAgency number in May-19, for large agencies...multiply that number against the number of agencies that joined in May-19...then multiply that result by 5"

 

These are just calculations that represent the kind of layered calculations (and some of the calculations would have 3-5 interim calculations involved) that I'd be looking at doing.  I need to refresh my understanding of general approach that SQL and Excel data models allows for this.  I know how to do it with a series of pivot tables, but that doesn't seem efficient.

 

This is obviously a fraction of the data, so even though something may appear only once here, in the full dataset, the aggregation would actually yield math that I can't do on a calculator (which for this data extract I can do).  Don't anyone thinking I'm trying to run a data model on 50 lines of data.   :)

 

Thanks for helping me get back on my feet with SQL and Excel modeling.  As I read about it, the potential seems there to transform the way I do complex calculations.  Just need to understand how the data itself needs to be structured in Excel, and then a quick few pointers on how query language handles the kinds of things I'm looking to calculate.

3 Replies

Hi @txrussianguy ,

 

In general Excel is not SQL tool, you may use SQL query in connectors to SQL databases. In Excel to transform data is used Power Query, it has its own M-script language and rich user interface. I many cases only UI is enough to transform data, in particular transformations as you say could be done within it. Result is returned back to Excel sheet as Excel table.

 

Data model (Power Pivot and DAX language) if you'd like to add any measures and return result back as PivotTable (however, could be also Excel table).

 

Not sure about file size, don't think you reduce it compare with PivotTables only, especially if you don't save PivotTable cache within the file.

@Sergei Baklanthanks very much.  I never save cache on pivots, and figured that this might be my easier near-term solution. 

@txrussianguy , yes, if you uncheck this default setting file size could be reduced significantly