Excel: connect with AS cube to get cardinality

%3CLINGO-SUB%20id%3D%22lingo-sub-2536799%22%20slang%3D%22en-US%22%3EExcel%3A%20connect%20with%20AS%20cube%20to%20get%20cardinality%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2536799%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Team%2C%26nbsp%3B%20I'm%20connecting%20excel%20workbook%20with%20our%20production%20cube%20to%20get%20following%20details.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22TheTallman_0-1626061576623.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294918i413F387336ABAACD%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22TheTallman_0-1626061576623.png%22%20alt%3D%22TheTallman_0-1626061576623.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ethen%20I'm%20using%20this%20info%20to%20get%20cardinality%20and%20visible%20information%20and%20further%20using%20vlookup%20to%20put%20in%20recon%20workbook.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22TheTallman_1-1626061665630.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294919i88E4E9B04E459A60%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22TheTallman_1-1626061665630.png%22%20alt%3D%22TheTallman_1-1626061665630.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20make%20it%20automate%20through%20macro%20and%20expecting%20these%20things%20through%20command%20button%20..%3C%2FP%3E%3CP%3E1.%20make%20cube%20connection%20and%20once%20button%20pressed%20%2C%20output%20of%20this%20mdx%26nbsp%3B%20%22SELECT%20*%20FROM%20%24System.MDSCHEMA_DIMENSIONS%22%20should%20be%20pulled%20in%20excel%20and%20vlookup%20get%20active%20and%20put%20requested%20information%20in%20recon%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20file%20is%20attached%20.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2536799%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2536929%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20connect%20with%20AS%20cube%20to%20get%20cardinality%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2536929%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1095018%22%20target%3D%22_blank%22%3E%40TheTallman%3C%2FA%3E%26nbsp%3BNot%20sure%20what%20you%20trying%20to%20achieve%20with%20the%20formulae%20provided%20in%20your%20example%20workbook%2C%20as%20they%20are%20incorrectly%20applying%20the%20VLOOKUP%20function.%20It%20seems%20to%20work%20but%20that's%20only%20by%20coincidence%2Cas%20both%20data%20sets%20are%20sorted%20in%20the%20exact%20same%20way.%20I've%20corrected%20them%20in%20the%20attached%20workbook%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20my%20question%20would%20be%20why%20not%20just%20take%20the%20last%20three%20columns%20from%20the%20cube-output%20and%20paste%20in%20the%20recon%20sheet.%20No%20need%20for%20lookups%20at%20all.%20If%20you%20insist%2C%20you%20could%20record%20a%20macro%20picking-up%20all%20the%20consecutive%20steps%20needed.%20I.e.%20open%20the%20data%20file%2C%20copy%20the%20data%2C%20paste%20it%20in%20the%20%22Cube%20Output%22%20sheet%2C%20copy%20the%20last%20three%20columns%2C%20paste%20them%20into%20the%20%22Recon%22%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20if%20in%20reality%20it%20isn't%20all%20that%20simplistic%2C%20perhaps%20you%20can%20set-up%20PowerQuery%20(PQ)%20to%20connect%20to%20your%20data%20source%20and%20transform%2Fextract%20whatever%20sub-set%20of%20data%20you%20need.%20No%20need%20fro%20VBA%20or%20VLOOKUP.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi Team,  I'm connecting excel workbook with our production cube to get following details.

 

TheTallman_0-1626061576623.png

then I'm using this info to get cardinality and visible information and further using vlookup to put in recon workbook.

TheTallman_1-1626061665630.png

 

I want to make it automate through macro and expecting these things through command button ..

1. make cube connection and once button pressed , output of this mdx  "SELECT * FROM $System.MDSCHEMA_DIMENSIONS" should be pulled in excel and vlookup get active and put requested information in recon workbook.

 

Excel file is attached . 

 

2 Replies

@TheTallman Not sure what you trying to achieve with the formulae provided in your example workbook, as they are incorrectly applying the VLOOKUP function. It seems to work but that's only by coincidence,as both data sets are sorted in the exact same way. I've corrected them in the attached workbook,

 

Then, my question would be why not just take the last three columns from the cube-output and paste in the recon sheet. No need for lookups at all. If you insist, you could record a macro picking-up all the consecutive steps needed. I.e. open the data file, copy the data, paste it in the "Cube Output" sheet, copy the last three columns, paste them into the "Recon" sheet.

 

Now, if in reality it isn't all that simplistic, perhaps you can set-up PowerQuery (PQ) to connect to your data source and transform/extract whatever sub-set of data you need. No need fro VBA or VLOOKUP.

The PowerQuery option sounds the best and most efficient way of doing this, especially with large datasets.