Excel Services and On-Premises SQL Data

%3CLINGO-SUB%20id%3D%22lingo-sub-3263%22%20slang%3D%22en-US%22%3EExcel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3263%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20looking%20to%20have%20an%20active%20data%20connection%20from%20an%20Excel%20file%20in%20Excel%20Services%20on%20SharePoint%26nbsp%3BOnline%20(O365)%26nbsp%3Bthat%20has%20a%20data%20connection%20to%20an%20on-premises%20SQL%20database.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENeed%20to%20be%20able%20to%20have%20data%20refresh%20work%20like%20it%20does%20with%20SharePoint%20on-premises.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20DMG%20used%20for%20PowerBI%20doesn't%20appear%20to%20have%20connections%20for%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20%22out-of-the-box%22%20solution%20(or%20close%20to%20one).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20still%20a%20supported%20scenario%3F%20It%20was%20widely%20used%20in%20on-premises%20SharePoint%20system%20as%20is%20currently%20blocking%20some%20key%20Office%20365%20adoption.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3263%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-325373%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-325373%22%20slang%3D%22en-US%22%3E%3CP%3EI%20think%20the%20part%20that%20a%20bunch%20of%20us%20(myself%20included)%20were%20missing%20was%20adding%20the%20workbook%20to%20the%20PowerBI%20Workspace%20and%20then%20going%20under%20Settings%20and%20Workbooks%20and%20assigning%20the%20gateway%20to%20that%20workbook.%26nbsp%3B%20That%20is%20what%20tells%20the%20excel%20file%20where%20to%20actually%20look%20for%20it's%20refreshes!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20the%20question%20is%2C%20how%20can%20other%20users%20also%20refresh%20or%20are%20we%20at%20the%20mercy%20of%20the%20scheduled%20refreshes%20under%20my%20PowerBI%20User%3F%3F%3F%26nbsp%3B%20Currently%20we%20get%20an%20error%20when%20another%20user%20tries%20to%20refresh%20the%20pivot%20table...%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-263960%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-263960%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20John%2C%20did%20you%20get%20around%20to%20writing%20that%20blog%3F%26nbsp%3B%20%3B)%3C%2Fimg%3E%3C%2FP%3E%3CP%3EWe%20are%20working%20with%20a%20client%20that%20has%20an%20extensive%20powerpivot%20dashboard%20system%20that%20pulls%20data%20predominantly%20from%20a%20LOB%20on%20prem%20SQL%20database%20(syspro).%26nbsp%3B%20The%20goal%20would%20be%20that%20we%20can%20connect%20to%20the%20same%20SQL%20database%20(through%20means%20that%20you%20describe%20in%20place%20of%20the%20current%20odbc%20connections%20in%20the%20power%20pivot%20data%20model)%20and%20refresh%20on%20schedule%20so%20outside%20sales%20team%20can%20be%20viewing%20dashboards%20in%20sharepoint%20with%20daily%20current%20data.%26nbsp%3B%20Looking%20forward%20to%20your%20response!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%20Matt%40scouttg.com%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-193861%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-193861%22%20slang%3D%22en-US%22%3EI%20have%20tried%20this%20multiple%20times.%20If%20you%20still%20are%20monitoring%20this%20thread%20I%20sure%20would%20appreciate%20some%20help%20to%20understand%20how%20you%20accomplished%20this.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-114470%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-114470%22%20slang%3D%22en-US%22%3E%3CP%3EHm%20%2C%20to%20clarify%2C%20the%20scenario%20you%20think%20should%20work%20is%3F%3A%3C%2FP%3E%3CP%3ESharePoint%20Online%26gt%3BExcel%20File%26gt%3BExcel%20Power%20Pivot%26gt%3BData%20Gateway%26gt%3BonPrem%20Datasource%20(ie.SQL%20or%20SSAS)%3C%2FP%3E%3CP%3EWhat%20seems%20not%20working%20currently%20is%3A%3C%2FP%3E%3CP%3ESharePoint%20Online%26gt%3BExcel%20File%26gt%3BData%20Gateway%26gt%3BonPrem%20SSAS%20(Tabular%2FOLAP%20%22Live%2FDirect%20Connection%22)%3C%2FP%3E%3CP%3E--%26gt%3BTried%20by%20Connecting%20in%20Excel%20to%20onPrem%20SSAS%20%22direct%22%20(local%20Servername)%20and%20also%20via%20the%20ODC%20File%20(provided%20with%20%22analyse%20in%20excel%22)%2C%20in%20both%20cases%20clicking%20in%20the%20excel%20online%20pivot%20table%20throws%20error%2C%20the%20gateway%20seems%20not%20to%20be%20involved%3C%2FP%3E%3CP%3Esee%20also%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FIntegrations-with-Files-and%2FUse-Power-BI-as-Data-Source-for-Excel-and-Utilize-Data-Gateway%2Ftd-p%2F57934%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FIntegrations-with-Files-and%2FUse-Power-BI-as-Data-Source-for-Excel-and-Utilize-Data-Gateway%2Ftd-p%2F57934%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-114469%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-114469%22%20slang%3D%22en-US%22%3E%3CP%3EHm%20%2C%20to%20clarify%2C%20the%20scenario%20you%20think%20should%20work%20is%3F%3A%3C%2FP%3E%3CP%3ESharePoint%20Online%26gt%3BExcel%20File%26gt%3BExcel%20Power%20Pivot%26gt%3BData%20Gateway%26gt%3BonPrem%20Datasource%20(ie.SQL%20or%20SSAS)%3C%2FP%3E%3CP%3EWhat%20seems%20not%20working%20currently%20is%3A%3C%2FP%3E%3CP%3ESharePoint%20Online%26gt%3BExcel%20File%26gt%3BData%20Gateway%26gt%3BonPrem%20SSAS%20(Tabular%2FOLAP%20%22Live%2FDirect%20Connection%22)%3C%2FP%3E%3CP%3E--%26gt%3BTried%20by%20Connecting%20in%20Excel%20to%20onPrem%20SSAS%20%22direct%22%20(local%20Servername)%20and%20also%20via%20the%20ODC%20File%20(provided%20with%20%22analyse%20in%20excel%22)%2C%20in%20both%20cases%20clicking%20in%20the%20excel%20online%20pivot%20table%20throws%20error%2C%20the%20gateway%20seems%20not%20to%20be%20involved%3C%2FP%3E%3CP%3Esee%20also%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FIntegrations-with-Files-and%2FUse-Power-BI-as-Data-Source-for-Excel-and-Utilize-Data-Gateway%2Ftd-p%2F57934%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FIntegrations-with-Files-and%2FUse-Power-BI-as-Data-Source-for-Excel-and-Utilize-Data-Gateway%2Ftd-p%2F57934%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-11526%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-11526%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%26nbsp%3Bthat's%20good%20to%20know!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-11520%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-11520%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20personal%20gateway%20does%20allow%20for%20refresh%20and%20scheduled.%20It%20is%20only%20daily%20and%20limited%20to%2010K%20rows%20but%20it%20does%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-11498%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-11498%22%20slang%3D%22en-US%22%3EEric%20you%20mentioned%20earlier%20on%20that%20you%20are%20looking%20to%20avoid%20the%20power%20bi%20pro%20license.%20I%20thought%20that%20if%20you%20refresh%20via%20the%20gateway%2C%20then%20pro%20license%20becomes%20requirement%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-7263%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-7263%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20gained%20a%20bit%20of%26nbsp%3B%20headway%20on%20this%20model.%20I%20now%20have%20Exel%20files%20being%20updated%20from%20on-premises%20systems%20via%20the%20Power%20BI%20gateway.%26nbsp%3B%20That%20is%20a%20great%20win!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20model%20is%2C%20however%2C%20a%20bit%20different%20than%20I%20was%20thinking%20and%20different%20then%20what%20I%20believe%20is%20represented%20in%20this%20article%20(last%20option%20in%20the%20list)%20%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FUse-external-data-in-workbooks-in-SharePoint-Online-8d7f5dc6-8384-4d7d-b00f-b283c1e192ef%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FUse-external-data-in-workbooks-in-SharePoint-Online-8d7f5dc6-8384-4d7d-b00f-b283c1e192ef%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20end%20goal%20is%3A%20Open%20a%20web%20page%20in%20SharePoint%20with%20a%20Excel%20Web%20part%20and%20click%20refresh%20data%20and%20that%20data%20will%20refresh%20on%20demand%20from%20an%20on-premises%20data%20source.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhile%26nbsp%3BI%20can%20get%20the%20Excel%20file%20to%20refresh%20by%20setting%20it%20up%20in%20Power%20BI%20and%20refreshing%20from%20Power%20BI.%20I%20still%20can't%20get%20the%20%22refresh%20data%22%20button%26nbsp%3Bin%20Excel%20Online%2FExcel%20Services%20Online%20to%20work.%20Fails%20everytime.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20progressed%20quite%20a%20bit%26nbsp%3Bthanks%20to%20your%20help%20John.%20So%20thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-7008%22%20slang%3D%22en-US%22%3ERE%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-7008%22%20slang%3D%22en-US%22%3EActually%2C%20SPO%20has%20been%20using%20Excel%20Online%20(not%20Excel%20Services)%20for%20quite%20some%20time%20now.%20The%20Excel%20Services%20capabilities%20were%20moved%20over.%20Excel%20is%20still%20totally%20viable%20(and%20will%20be%20moving%20forward).%20It's%20a%20first%20class%20citizen%20and%20in%20most%20cases%20the%20preferred%20one%20for%20analysts.%20It%20works%20quite%20well%20with%20Power%20BI%20-%20it's%20really%20a%20better%20together%20story.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-5248%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-5248%22%20slang%3D%22en-US%22%3E%3CP%3El%20that%20you%20should%20need%20to%20do%20is%20to%20first%2C%20register%20that%20data%20source%20with%20the%20gateway%2C%20and%20then%20connect%20to%20it%20with%20Excel%20directly%2C%20as%20you%20would%20do%20normally).%20If%20the%20data%20source%20is%20registered%20correctly%2C%20it%20should%20%22just%20work%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-5103%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-5103%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20John%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20finally%20have%20the%20Data%20Gateway%20set%20up%20and%20am%20able%20to%20schedule%20Power%20BI%20refreshes.%20Yay!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20at%20loss%2C%20however%2C%20on%20how%20to%20connnect%20an%20Excel%20file%20to%20a%20data%20source%20in%20the%20Power%20BI%20Data%20Gateway.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20used%20the%20%22Analyze%20in%20Excel%22%20feature%20to%20get%20the%20ODC%20file%20which%20makes%20a%20connection%20to%20the%20Power%20BI%20data%20source%20through%20a%20URL.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20this%20was%20it%20but%20it%20fails%20to%20refresh%20in%20Excel%20Services.%20I%20believe%20because%20it%20links%20to%20a%20model%2C%20but%20I%20am%20not%20certain.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20advice%20on%20connecting%20Excel%20to%20the%20data%20source%20in%20Power%20BI%20Data%20Gateway%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3EeRic%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3562%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3562%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20is%20excellent%20news!%20Thank%20you%20very%20much%20John!%20This%20really%20does%20bring%20BI%20to%20SharePoint%20on%20Office%20365.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3525%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3525%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20Eric%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETHe%20part%20that%20you're%20missing%20is%20the%20On-Prem%20data%20gateway%20from%20Power%20BI.%20With%20that%20(once%20configured)%20Excel%20in%20Office%20365%20will%20refresh%20from%20on%20prem%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3518%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3518%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20gets%20tricky%20as%20the%20Excel%20Services%20Service%20Application%20is%20actually%20performing%20the%20refresh%20in%20this%20case.%20It%20would%20typically%20use%20the%20Secure%20Store%20Target%20Application%20profile%20configured%20for%20data%20access%20as%20designated%20in%20the%20Excel%20spreadsheet.%20This%20is%20basically%20how%20reporting%20works%20in%20Project%20Server.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20haven't%20tried%20to%20do%20this%20setup%20with%20Office%20365%20but%20I%20think%20you%20can%20set%20up%20a%20SSS%20profile%20in%20the%20SharePoint%20Admin%20portal.%20I'd%20be%20curious%20to%20see%20if%20it%20works%20with%20the%20gateway.%20If%20it%20does%2C%20this%20opens%20up%20some%20new%20possibilities.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E--Treb%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3515%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3515%22%20slang%3D%22en-US%22%3E%3CP%3EAre%20you%20opening%20the%20Excel%20file%20on%20the%20desktop%20to%20refresh%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20trick%20I%20still%20haven't%20figured%20is%20being%20able%20to%20refresh%20the%20Excel%20file%20in%20Excel%20Services%20Online%2C%20connected%20to%20on-prem%20SQL%2C%20without%20opening%20the%20Excel%20file%20in%20the%20client%20application%20to%20get%20the%20data%20refresh.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20refreshing%20from%20the%20Excel%20Web%20Part%20on%20a%20SharePoint%20page.%20A%20great%20SharePoint%20BI%20tool!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%20great%20if%20everything%20is%20on-prem%20or%20everything%20is%20in%20the%20cloud%20(Excel%20file%20on%20One%20Drive%20or%20SharePoint%20Online%20or%20SQL%20Azure).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20Online%20to%20On-Prem%20just%20using%20Excel%20Services%20Online%20that%20is%20still%20getting%20me.%20Trying%20to%20accomplish%20without%20the%20Power%20BI%20Pro%20license%20too.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20greatly%20appreciate%20your%20time%20and%20the%20info%20you%20provided!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3442%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3442%22%20slang%3D%22en-US%22%3E%3CP%3EI%20should%20also%20add%2C%20because%20I%20just%20tested%20it%2C%20and%20it%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20Excel%20workbook%20that%20is%20connected%20directly%20to%20a%20SQL%20server%2C%20and%20then%20connected%20to%20Power%20BI%20(either%20from%20OneDrive%20or%20a%20SharePoint%20library)%20will%20be%20able%20to%20connect%20to%20the%20data%20through%20the%20on%20On-Prem%20Data%20Gateway.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20short%2C%20it%20works.%20I%20think%20that%20I%20need%20to%20do%20a%20blog%20post%20on%20this%20one%20-%20there%20are%20a%20few%20intricacies%20involved.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3430%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3430%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you're%20talking%20about%20Excel%20Online%20in%20Office%20365%20connecting%20to%20on%20prem%20data%2C%20that%20is%20possible%20with%20the%26nbsp%3BGateway%20if%20you're%20using%20PowerPivot.%20You%20can%20use%20Power%20BI%20to%26nbsp%3Bschedule%20an%20Excel%20Workbook%20refresh%20from%20on%20premises%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you're%20talking%20about%20on%20prem%20Excel%20Services%20or%20Excel%20Online%2C%20you%20can%20configure%20the%20data%20connection%20to%20refresh%20on%20open.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3367%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3367%22%20slang%3D%22en-US%22%3EDefinitively%2C%20Power%20BI%20has%20to%20be%20the%20way%20to%20go%20since%20I%20expect%20Excel%20Services%20will%20dissapear%20at%20some%20time%20from%20SPO%20as%20it%20happens%20in%20SP%202016%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-896798%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-896798%22%20slang%3D%22en-US%22%3E%22and%20then%20going%20under%20Settings%20and%20Workbooks%20and%20assigning%20the%20gateway%22%20--%26gt%3B%20Hm%2C%20i%20can't%20find%20this%20dialog%2C%20maybe%20UI%20has%20changed%20again%20since%20your%20post%20date%3F!%20Can%20you%20publish%20a%20screenshoot%3F%20I'm%20also%20trying%20a%20XLS%20File%20with%20PowerBI.com%20as%20datasource%20(Analyse%20in%20Excel%20Feature).%20Neither%20publishing%20this%20file%20as%20workbook%20to%20BPI%20nor%20publishing%20this%20file%20to%20sharepoint%20online%20leads%20to%20an%20refreshable%20solution.%20In%20this%20scenario%20no%20gateway%20would%20be%20needed%20but%20this%20still%20not%20works%20%3A(%3C%2Fimg%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900255%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900255%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F85637%22%20target%3D%22_blank%22%3E%40Jochen%20Juelke%3C%2FA%3E%2C%20I%20was%20going%20to%20test%20for%20you%20today%2C%20but%20it%20appears%20there%20is%20an%20issue%20with%20getting%20workbooks%20from%20sharepoint%20sites%20currently%2C%20as%20per%20this%20link%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FService%2FService-Get-Data-gt-Sharepoint-quot-The-URL-is-not-valid-or-you%2Ftd-p%2F775706%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FService%2FService-Get-Data-gt-Sharepoint-quot-The-URL-is-not-valid-or-you%2Ftd-p%2F775706%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20added%20a%20new%20workbook%20from%20my%20OneDrive%20for%20Business%20which%20works%20fine%20right%20now%20(same%20thing%20otherwise...).%26nbsp%3B%20One%20thing%20to%20note%2C%20when%20you%20add%20it%20there%20are%20two%20options%2C%20import%20or%20connect%2C%20this%20is%20Connect.%20Here%20is%20a%20link%20(since%20I%20can't%20upload%20a%20screenie%20apparently...)%20to%20the%20screenshot%20of%20my%20Power%20BI%20settings%20screen%3A%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Flinkatech365-my.sharepoint.com%2F%3Ai%3A%2Fg%2Fpersonal%2Fmatt_scouttg_com%2FEYJUGCXj8AdPuQLPrXuckEIBpYhqHlNSm6hXjkDX-ItCeA%3Fe%3DeF5rhr%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EPower%20BI%20workbook%20gateway%20settings.jpg%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-901417%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Services%20and%20On-Premises%20SQL%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-901417%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F217320%22%20target%3D%22_blank%22%3E%40MATT%20DRYFHOUT%3C%2FA%3E%26nbsp%3BTHANK%20YOU!%20I%20always%20searched%20for%20the%20workbook-properties-dialog%20while%20the%20excel%20file%20is%20opened%2Frendered%20in%20browser.%20But%20we%20must%20navigate%20as%20following%3A%3C%2FP%3E%3CP%3E-%20go%20to%20workspace%20overview%20(where%20Dasbords%2CReports%2CWorkbooks%2CDatasets%20tabs%20are%20displayed)%26nbsp%3B%3C%2FP%3E%3CP%3E-%20navigate%20NOT%20to%20Worksbooks%20tab%20and%20chosse%20%22workbook%26gt%3Bsettings%22%20there!%3C%2FP%3E%3CP%3E-%20but%20instead%20navigate%20to%20DATASETS%20tab%20and%20choose%20%22settings%22%20for%20anyone%20of%20your%20datasets%3C%2FP%3E%3CP%3E-%20Now%20you%20can%20switch%20to%20%22Workbooks%22%20tab%20where%20gateway%20can%20be%20configured%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFollow%20Up%20Question%3A%3C%2FP%3E%3CP%3EDo%20you%20know%20any%20trick%20how%20to%20get%20the%20scenario%20working%20when%20the%20excel%20file%20is%20not%20based%20on%20a%20onPrem%20Datasource%20but%20uses%20a%20published%20PBI%20dataset%20as%20source%20(created%20via%20analyse%20in%20excel%20feature)%3F%3C%2FP%3E%3CP%3EWe%20want%20to%20create%20a%20%22dashboad%22%20with%20content%20auf%20PBI%20Reports%20and%20XL%20Reports%2C%20both%20reports%20should%20be%20based%20on%20the%20same%20dataset%20(hosted%20in%20PBI).%3CBR%20%2F%3EAlso%20this%20scenario%20seens%20simpler%20then%20the%20last%20(because%20no%20gateway%20is%20needed)%20i%20found%20no%20way%20to%20get%20this%20to%20work.%20After%20publishing%20a%20XLS%2C%20which%20has%20a%20Online%20PBI%20Dataset%20as%20source%2C%20PBI%20Service%20says%20%22Data%20Refresh%20is%20not%20supported!%20But%20why%3F%20My%20explanation%20is%20that%20%22asazure%3A%2F%2F%22%20is%20used%20in%20the%20ConStr%20and%20it%20seems%20that%20is%20not%20jet%20supported.%20Do%20you%20have%20more%20insights%20on%20that%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20871px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F136031i77117FB5568F940D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22XLSRefershFromPBIDataset.png%22%20title%3D%22XLSRefershFromPBIDataset.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Contributor

I am looking to have an active data connection from an Excel file in Excel Services on SharePoint Online (O365) that has a data connection to an on-premises SQL database.

 

Need to be able to have data refresh work like it does with SharePoint on-premises.

 

The DMG used for PowerBI doesn't appear to have connections for Excel.

 

I am looking for "out-of-the-box" solution (or close to one). 

 

Is this still a supported scenario? It was widely used in on-premises SharePoint system as is currently blocking some key Office 365 adoption.

 

Thanks!

22 Replies
Highlighted
"and then going under Settings and Workbooks and assigning the gateway" --> Hm, i can't find this dialog, maybe UI has changed again since your post date?! Can you publish a screenshoot? I'm also trying a XLS File with PowerBI.com as datasource (Analyse in Excel Feature). Neither publishing this file as workbook to BPI nor publishing this file to sharepoint online leads to an refreshable solution. In this scenario no gateway would be needed but this still not works :(
Highlighted

Hey @Jochen Juelke, I was going to test for you today, but it appears there is an issue with getting workbooks from sharepoint sites currently, as per this link:

https://community.powerbi.com/t5/Service/Service-Get-Data-gt-Sharepoint-quot-The-URL-is-not-valid-or...

 

However, I added a new workbook from my OneDrive for Business which works fine right now (same thing otherwise...).  One thing to note, when you add it there are two options, import or connect, this is Connect. Here is a link (since I can't upload a screenie apparently...) to the screenshot of my Power BI settings screen:

 

Power BI workbook gateway settings.jpg 

 

Highlighted

@MATT DRYFHOUT THANK YOU! I always searched for the workbook-properties-dialog while the excel file is opened/rendered in browser. But we must navigate as following:

- go to workspace overview (where Dasbords,Reports,Workbooks,Datasets tabs are displayed) 

- navigate NOT to Worksbooks tab and chosse "workbook>settings" there!

- but instead navigate to DATASETS tab and choose "settings" for anyone of your datasets

- Now you can switch to "Workbooks" tab where gateway can be configured

 

Follow Up Question:

Do you know any trick how to get the scenario working when the excel file is not based on a onPrem Datasource but uses a published PBI dataset as source (created via analyse in excel feature)?

We want to create a "dashboad" with content auf PBI Reports and XL Reports, both reports should be based on the same dataset (hosted in PBI).
Also this scenario seens simpler then the last (because no gateway is needed) i found no way to get this to work. After publishing a XLS, which has a Online PBI Dataset as source, PBI Service says "Data Refresh is not supported! But why? My explanation is that "asazure://" is used in the ConStr and it seems that is not jet supported. Do you have more insights on that?

XLSRefershFromPBIDataset.png