SQL Server Using Power BI in Hybrid IT云计算.docx

SQL Server Using Power BI in Hybrid IT云计算.docx

《SQL Server Using Power BI in Hybrid IT云计算.docx》由会员分享，可在线阅读，更多相关《SQL Server Using Power BI in Hybrid IT云计算.docx（36页珍藏版）》请在冰豆网上搜索。

SQLServerUsingPowerBIinHybridIT云计算

UsingPowerBIinaHybridEnvironment

Summary:

Manyorganizationsaretakingadvantageofthebenefitsofcloudcomputingsuchaslowercapitalexpendituresandincreasedagility,whilestillmaintainingdatainon-premisesdatastores.Thispaperdescribesbestpracticesforsecurity,networking,andcomponentarchitectureforbuildingahybridbusinessintelligence（BI）environmentbyusingacombinationofMicrosoftOffice365,MicrosoftAzure,andon-premisesdatasources.

Writers:

JosephD'Antoni,StaciaMisner

TechnicalReviewers:

EdPrice,CarlaSabotta,CindyGross,OlivierMatrat,KayUnkroth,DerrickVanArnam,AdamSaxton,DavidTorres,GregLow,RossLoForte

Published:

August,2014

Appliesto:

SQLServer,MicrosoftPowerBI,MicrosoftAzure

Copyright

Thisdocumentisprovided“as-is”.Informationandviewsexpressedinthisdocument,includingURLandotherInternetWebsitereferences,maychangewithoutnotice.Youbeartheriskofusingit.

Someexamplesdepictedhereinareprovidedforillustrationonlyandarefictitious. Norealassociationorconnectionisintendedorshouldbeinferred.

ThisdocumentdoesnotprovideyouwithanylegalrightstoanyintellectualpropertyinanyMicrosoftproduct.Youmaycopyandusethisdocumentforyourinternal,referencepurposes.

©2014Microsoft.Allrightsreserved.

TableofContents

Introduction4

CloudComputingDefinitions4

Cloud-OnlyModel5

Azure6

Office3656

HybridCloudModel7

HybridArchitecture10

HybridEnvironmentwithPaaS10

HybridEnvironmentwithIaaS10

AzureVirtualNetwork10

AzureVMs12

Security14

Office365Security14

AzureSecurity15

ActiveDirectoryIntegration15

ActiveDirectoryandOffice36515

ActiveDirectoryandAzureIaaS17

Office365andAzureSecurityCompliance17

PowerBI18

PowerBIFeatures18

PowerBIinExcel18

PowerBIforOffice36519

PowerBIDataRefresh20

ODataFeedsforNon-RefreshableDataSources21

PowerBICloudDataSources21

AzureVMRunningSQLServerasaDataSource24

DataManagementGateway24

DataSourceRegistration25

ODataFeedsfromPowerBIDataSources28

PowerQueryDataRefreshforOn-PremisesData31

Conclusion31

AuthorBios32

Introduction

ThisdocumentdetailsthetasksnecessarytoimplementMicrosoftPowerBIforOffice365inahybridenvironment.Itdescribesbestpracticesforsecurity,networking,andcomponentarchitectureforbuildingahybridbusinessintelligence（BI）environmentbyusingacombinationofMicrosoftOffice365,MicrosoftAzure,andon-premisesdatasources.

Inaddition,wedescribeoptionsandbestpracticesforeachserviceyoumightincludeinahybridenvironment.Thisdocumentalsoprovidesguidanceforintegratingsecurityandidentitymanagementinahybridenvironment,aswellashowtoconfigurePowerBItorefreshdatafromsupportedsourceswithinthisenvironment.

NoteBecauseAzureservicescanchangefrequently,pleaserefertotheMicrosoftAzurewebsitefortheofferingsandrangeoffeaturescurrentlyavailable.

Thegoalofthisdocumentistohelpyouunderstandhowtodesignanarchitectureinwhichtheseservicescansecurelyinteractnotonlywitheachother,butalsowithyouron-premisesenvironment.

Thetargetaudienceforthisdocumentissystemandnetworkadministratorsaswellasbusinessintelligence（BI）solutionarchitectsanddeveloperswhowanttoimplementPowerBIforOffice365inahybridenvironment.Togetthemostoutofthisdocument,youshouldhaveabasicunderstandingofvirtualprivatenetwork（VPN）connectionsandcloudcomputing.Tolearnmoreaboutcloudcomputing,seeMicrosoftAzure,theMicrosoftAzureInfographic（allAzureInfographics）,andGetStartedwithOffice365.TolearnmoreaboutVPNs,seeConnectanon-premisesnetworktoaMicrosoftAzurevirtualnetwork.

CloudComputingDefinitions

Thereareafewtermsthatareuniquetocloudcomputing.Inthisdocument,thefollowingdefinitionsapply:

CloudComputingTheNationalInstituteforStandardsandTechnologydefinescloudcomputingasamodelfor“enablingubiquitous,convenient,on-demandnetworkaccesstoasharedpoolofcomputingresources（e.g.networks,servers,storage,applications,andservices）thatcanberapidlyprovisionedandreleasedwithminimalmanagementeffortorserviceproviderinteraction.”

InfrastructureasaService（IaaS）Thecapabilitytoprovisionstorage,archive,network,andserverresourcesinasharedenvironment.Typically,serverresourcesaredeployedasvirtualmachines（VMs）.Themanagementoftheserveroperatingsystemsandotherresourceswithinthisenvironmentisperformedbythecustomer,butthemanagementoftheunderlyinghardwareandhypervisorisperformedbytheprovider.AzureVirtualMachinesandAzureVirtualNetworksarekeycomponentsofIaaSavailableinAzure.

HybridCloudAninfrastructurethatbridgeson-premisescomputingresourcesandapubliccloud.Ahybridcloudenvironmentmaybeusedforfunctionalitysuchascloudbursting（i.e.sendingaworkloadtothecloudwhenon-premisesresourceshavebeenexhausted）ordisasterrecovery.

PlatformasaService（PaaS）Thecapabilityforthecustomertodeployapplicationtiers,suchasadatabaseorawebservice,toacontainermanagedbytheprovider.Thecustomerdoesnotmanageoperatingsystemsorinfrastructureinthismodel,buthascontrolovertheirapplication,data,andpossiblysomeconfigurationsettings.AzureSQLDatabaseorHDInsightareexamplesofPaaSinAzure.

PowerBIThecombinedMicrosoftself-servicebusinessintelligencesolution.PowerBIisaseparateserviceforOffice365andincludesseveralfeaturesinExcel2013（PowerPivot,PowerView,PowerQuery,andPowerMap）.ForthedifferencesbetweenPowerBIinExcel2013andPowerBIforOffice365,seethePowerBIsectionbelow.

PublicCloudCloudresourcesareavailableforusebythegeneralpublicandexistinthedatacentersofthecloudprovider.

PrivateCloudAvirtualizationinfrastructureprovisionedforusebyasingleorganizationordepartmentwithinthatorganization.Itmayexistonorofftheorganization’spremises.

SoftwareasaService（SaaS）Thecapabilityforthecustomertousetheprovider’sapplicationsrunningontheprovider’scloudinfrastructure.TheapplicationsarebroadlyaccessiblefromanInternetbrowser.Althoughthecustomerdoesnotmanageanyoftheunderlyinginfrastructure,thecustomermanagescontentandpossiblysomelimitedconfigurationsettings.Office365isanexampleofanSaaSoffering.

Cloud-OnlyModel

Microsoftoffersavarietyofservicesthatyoucanuseindependentlyorincombinationwithoneanothertoarchitectabusinessintelligencesolutioninthecloud.AportionofthistypeofsolutionreliesonIaaSorPaaSservicesavailablethroughAzure,whiletheremainderofthesolutionreliesonOffice365,asshowninFigure1.Althoughtheseservicesareallcloud-based,it’simportanttounderstandthatAzureandOffice365operateinseparatecontainers,includingthenetworkandauthenticationlayers.

Figure1:

Office365andAzureservicessupportbusinessintelligenceinthecloudinseparatecontainers

Azure

AzureservicesincludebothIaaSandPaaSofferings.YoucanrunSQLServerinaVMthatyoumanageinternallyandthenuseaVirtualNetworktosecurelyandreliablyaccessitfromotherVMsinAzureorfromresourcesinyouron-premisesnetwork.Asanalternative,youcanuseSQLDatabaseasacloud-basedrelationaldatabaseplatformthatrequiresminimalmanagement.Toworkwithbigdatainthecloud,youcanuseHDInsight,Microsoft’sHadoop-basedservice.

Office365

Office365isanSaaSofferingthatincludestheentireMicrosoftOfficesuite.WithOffice365,yourorganizationnolongerneedstomanageinfrastructureforMicrosoftSharePointandMicrosoftExchange,andcanstilldeliverarich,interactivebusinessintelligenceexperiencebyprovidinguserswithPowerBI.Thisexperiencebeginswhenuserscreateworkbookscontainingdatamodels,reports,businessformulas,KPIs,andvisualizationsbyusingoneormoreofthefollowingself-serviceBItoolsinExcel2013:

PowerQuery,PowerPivot,PowerView,andPowerMap.Userscanthenshare,explore,andmanagetheseworkbooksonlinebyusingthefollowingPowerBIforOffice365features:

PowerBIsites,PowerBIQ&A,QueryandDataManagement,andthePowerBIapp.ThedatamodelsstoredintheworkbookscanuseasingledatasourceorcombinemultipledisparatedatasourcesobtainedfromwebsitesorAzureresourcesinacloud-onlymodel.

HybridCloudModel

Mostorganizationsarenotmovingtheirentireinfrastructuretothecloud.Whilethisoptionmightbeviableforstartupcompanies,largerestablishedfirmshavinganexistinginvestmentininfrastructurearemotivatedbythesavingsandagilitytheygainwhentransitioningtocloudofferings.Themostcommoncloud-basedinfrastructurescenariosatthetimeofthiswritingareamixofIaaSandSaaS.PaaSofferingshavehadsloweradoptionrates.BothIaaSandSaaSareoftenbettercandidatesfornewdevelopmentwork.Inparticular,IaaSiseasiertousewithexistingapplications.Ontheotherhand,manyorganizationsareconsideringPaaSfordevelopingfutureapplications.

Meanwhile,ahybridcloudinfrastructureisagoodmodelfororganizationsnewtocloudcomputing,whethertheywanttostartsimplywithadisasterrecoverysolutionforanon-premisesSQLServerorimplementfull-fledgedBIsolutionsthatrelyonPowerBI.Therearefourdifferentarchitecturalscenarios（variants）fromwhichyoucanchoosewhenimplementingPowerBIinahybridenvironment:

∙SaaS,IaaS,PaaS,andOn-PremisesWiththisarchitecture（sho