%3CLINGO-SUB%20id%3D%22lingo-sub-1940720%22%20slang%3D%22en-US%22%3EDeploy%20an%20End-to-End%20Azure%20Synapse%20Analytics%20and%20Power%20BI%20Solution%20using%20CMS%20Medicare%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1940720%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20many%20people%2C%20hands-on%20experience%20is%20often%20the%20best%20way%20to%20learn%20and%20evaluate%20data%20tools.%20I've%20been%20working%20with%20a%20colleague%20from%20our%20Azure%20team%2C%20Kunal%20Jain%2C%20to%20put%20together%20an%20end-to-end%20Azure%20Synapse%20and%20Power%20BI%20solution%20using%20120%2B%20million%20rows%20of%20real%20CMS%20Medicare%20Part%20D%20Data%20that%20is%20%3CA%20href%3D%22https%3A%2F%2Fwww.cms.gov%2FResearch-Statistics-Data-and-Systems%2FStatistics-Trends-and-Reports%2FMedicare-Provider-Charge-Data%2FDownloads%2FPrescriber_Methods.pdf%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Eavailable%20for%20use%20in%20the%20public%20domain%3C%2FA%3E.%20If%20you're%20not%20highly%20technical%2C%20and%20you've%20never%20used%20Azure%20or%20Power%20BI%20before%2C%20you%20can%20still%20deploy%20this%20solution%20with%20a%20few%20simple%20steps%20using%20an%20Azure%20ARM%20template.%20We%20also%20provide%20a%20video%20to%20walk%20you%20through%20a%20paint-by-numbers%20tutorial.%20The%20Azure%20ARM%20template%20will%20automatically%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ECreate%20Azure%20Data%20Lake%2C%20Azure%20Data%20Factory%2C%20and%20Azure%20Synapse%3C%2FLI%3E%0A%3CLI%3EPull%20the%20raw%20data%20from%20CMS%20into%20a%20Data%20Lake%20using%20Azure%20Data%20Factory%3C%2FLI%3E%0A%3CLI%3EShape%20the%20data%20and%20create%20a%20dimensional%20model%20for%20Azure%20Synapse%3C%2FLI%3E%0A%3CLI%3EDeploy%20the%20solution%20to%20Azure%20Synapse%2C%20including%20performance%20tuning%20settings%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EThe%20entire%20process%20takes%20about%20an%20hour%20and%20a%20half%20to%20run%2C%20with%20most%20of%20that%20time%20spent%20on%20waiting%20for%20the%20scripts%20from%20the%20ARM%20template%20to%20finish.%20Once%20deployed%2C%20another%20video%20also%20walks%20you%20through%20the%20steps%20of%20connecting%20it%20to%20a%20pre-built%20Power%20BI%20report%20template.%20The%20whole%20process%20should%20take%20about%201-2%20hours%20with%20no%20code%20required%2C%20and%20at%20the%20end%20you%20can%20review%20and%20evaluate%20and%20end-to-end%20Azure%20and%20Power%20BI%20solution%20using%20real%20CMS%20Medicare%20Part%20D%20Data%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fazure.microsoft.com%2Fen-us%2Fservices%2Fdata-factory%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Data%20Factory%3C%2FA%3E%26nbsp%3Bfor%20ELT%20packages%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fazure.microsoft.com%2Fen-us%2Fsolutions%2Fdata-lake%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Data%20Lake%3C%2FA%3E%26nbsp%3Bfor%20Unstructured%20Big%20Data%20Storage%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Fmassively-parallel-processing-mpp-architecture%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Synapse%20Analytics%3C%2FA%3E%26nbsp%3Bfor%20an%20MPP%20Cloud%20Data%20Warehouse%3C%2FLI%3E%0A%3CLI%3EPower%20BI%20for%20an%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fpowerbi.microsoft.com%2Fen-us%2Fblog%2Fpower-bi-premium-and-azure-analysis-services%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAnalysis%20Services%20Semantic%20Model%3C%2FA%3E%20and%20Data%20Visualizations%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EHere%20is%20a%20link%20to%20the%20GitHub%20site%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2Fkunal333%2FE2ESynapseDemo%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2Fkunal333%2FE2ESynapseDemo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhile%20the%20source%20CMS%20data%20is%20real%20public%20Healthcare%20data%2C%20the%20intent%20of%20this%20project%20is%20to%20provide%20you%20with%20a%20simple%20end-to-end%20solution%20for%20the%20purposes%20of%20learning%2C%20demos%2C%20and%20tool%20evaluation.%20We%20intend%20to%20enhance%20and%20build%20upon%20this%20solution%20in%20the%20future%2C%20but%20it%20is%20not%20a%20supported%20solution%20intended%20to%20be%20used%20for%20production%20purposes.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBelow%20is%20a%20tutorial%20video%20for%20deploying%20the%20solution.%20Note%20that%20this%20is%20designed%20to%20be%20low%20code%20with%20only%20a%20few%20things%20to%20cut%20and%20paste.%20All%20you%20need%20is%20an%20Azure%20account%2C%20and%20you%20can%20pause%20the%20Synapse%20instance%20or%20delete%20the%20entire%20Resource%20Group%20at%20any%20time.%20There%20are%20also%20simple%20instructions%20on%20the%20GitHub%20page%3A%3C%2FP%3E%0A%3CP%3E%3C%2FP%3E%3CDIV%20class%3D%22video-embed-center%20video-embed%22%3E%3CIFRAME%20class%3D%22embedly-embed%22%20src%3D%22https%3A%2F%2Fcdn.embedly.com%2Fwidgets%2Fmedia.html%3Fsrc%3Dhttps%253A%252F%252Fwww.youtube.com%252Fembed%252F-YnF2EHzTzs%253Ffeature%253Doembed%26amp%3Bdisplay_name%3DYouTube%26amp%3Burl%3Dhttps%253A%252F%252Fwww.youtube.com%252Fwatch%253Fv%253D-YnF2EHzTzs%26amp%3Bimage%3Dhttps%253A%252F%252Fi.ytimg.com%252Fvi%252F-YnF2EHzTzs%252Fhqdefault.jpg%26amp%3Bkey%3Dfad07bfa4bd747d3bdea27e17b533c0e%26amp%3Btype%3Dtext%252Fhtml%26amp%3Bschema%3Dyoutube%22%20width%3D%22600%22%20height%3D%22338%22%20scrolling%3D%22no%22%20title%3D%22YouTube%20embed%22%20frameborder%3D%220%22%20allow%3D%22autoplay%3B%20fullscreen%22%20allowfullscreen%3D%22true%22%3E%3C%2FIFRAME%3E%3C%2FDIV%3E%3CP%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20another%20tutorial%20video%20describing%20the%20process%20by%20which%20to%20connect%20the%20Power%20BI%20Template%20file%20containing%20the%20business%20logic%3A%3C%2FP%3E%0A%3CP%3E%3C%2FP%3E%3CDIV%20class%3D%22video-embed-center%20video-embed%22%3E%3CIFRAME%20class%3D%22embedly-embed%22%20src%3D%22https%3A%2F%2Fcdn.embedly.com%2Fwidgets%2Fmedia.html%3Fsrc%3Dhttps%253A%252F%252Fwww.youtube.com%252Fembed%252F_mslQZM7NrU%253Ffeature%253Doembed%26amp%3Bdisplay_name%3DYouTube%26amp%3Burl%3Dhttps%253A%252F%252Fwww.youtube.com%252Fwatch%253Fv%253D_mslQZM7NrU%26amp%3Bimage%3Dhttps%253A%252F%252Fi.ytimg.com%252Fvi%252F_mslQZM7NrU%252Fhqdefault.jpg%26amp%3Bkey%3Db0d40caa4f094c68be7c29880b16f56e%26amp%3Btype%3Dtext%252Fhtml%26amp%3Bschema%3Dyoutube%22%20width%3D%22600%22%20height%3D%22337%22%20scrolling%3D%22no%22%20title%3D%22YouTube%20embed%22%20frameborder%3D%220%22%20allow%3D%22autoplay%3B%20fullscreen%22%20allowfullscreen%3D%22true%22%3E%3C%2FIFRAME%3E%3C%2FDIV%3E%3CP%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20following%20diagram%20summarizes%20the%20steps%20of%20the%20whole%20process%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Source%20to%20Target.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236660iE71553F66D6197E9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Source%20to%20Target.png%22%20alt%3D%22Source%20to%20Target.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAzure%20Data%20Factory%2C%20along%20with%20Power%20BI%2C%20creates%20the%20following%20logical%20model%20that%20enables%20highly%20performant%20end%20user%20queries%20for%20complicated%20questions%20about%20the%20data.%20Notice%20that%20a%20CSV%20file%20is%20also%20added%20to%20the%20Power%20BI%20layer%20to%20demonstrate%20that%20custom%20criteria%20from%20a%20business%20user%20can%20be%20used%20to%20query%20the%20Synapse%20data%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Logical%20Model.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236661iC17985A4E9DCAE31%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Logical%20Model.png%22%20alt%3D%22Logical%20Model.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3BCalculations%20have%20been%20added%20to%20the%20Power%20BI%20Semantic%20Layer%20to%20enable%20complex%20analytics%20such%20as%20Pareto%20Analysis%3A%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Calculations.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236662i0E9A502D1D29D282%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Calculations.png%22%20alt%3D%22Calculations.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3BBelow%20is%20a%20screenshot%20of%20the%20pre-built%20Power%20BI%20report%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Dashboard%20Image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236663iEBB771B903192657%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Dashboard%20Image.png%22%20alt%3D%22Dashboard%20Image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMore%20information%20about%20the%20solution%20is%20available%20at%20the%20GitHub%20site%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2Fkunal333%2FE2ESynapseDemo%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2Fkunal333%2FE2ESynapseDemo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20deploy%20this%20solution%2C%20we'd%20appreciate%20if%20you%20could%20take%20the%20time%20to%20provide%20some%20feedback.%20What%20was%20your%20experience%20with%20the%20ARM%20template%3F%20How%20do%20you%20plan%20to%20use%20this%20solution%3F%20What%20types%20of%20similar%20solutions%20can%20we%20provide%20in%20the%20future%20that%20would%20be%20valuable%3F%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CIFRAME%20src%3D%22https%3A%2F%2Fforms.office.com%2FPages%2FResponsePage.aspx%3Fid%3Dv4j5cvGGr0GRqy180BHbRzszGDC4CeZJmrpyVKLIQE5UMEIwWURPRzQ1VVVZUDFPMjdHSEkyNEI0Ti4u%26amp%3Bembed%3Dtrue%22%20width%3D%22640px%22%20height%3D%22480px%22%20frameborder%3D%220%22%20allowfullscreen%3D%22allowfullscreen%22%20webkitallowfullscreen%3D%22webkitallowfullscreen%22%20style%3D%22border%3A%20none%3B%20max-width%3A%20100%25%3B%20max-height%3A%20100vh%3B%22%20marginwidth%3D%220%22%20marginheight%3D%220%22%20mozallowfullscreen%3D%22mozallowfullscreen%22%20msallowfullscreen%3D%22%22%3E%20%3C%2FIFRAME%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1940720%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Dashboard%20Image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236647i7F6F3B803D7BAE8F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Dashboard%20Image.png%22%20alt%3D%22Dashboard%20Image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAnyone%20can%20use%20this%20Template%20to%20Deploy%20an%20end-to-end%20Azure%20Synapse%20Analytics%20and%20Power%20BI%20demo%20solution%20using%20Real%20CMS%20data.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1940720%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eazure%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EHealthcare%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2045893%22%20slang%3D%22en-US%22%3ERe%3A%20Deploy%20an%20End-to-End%20Azure%20Synapse%20Analytics%20and%20Power%20BI%20Solution%20using%20CMS%20Medicare%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2045893%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20get%20as%20far%20as%20running%20the%26nbsp%3BdeploySynapseStorageADF.ps1%20script%26nbsp%3B%20step%2013%2F15%20I%20think%20line%20219.%26nbsp%3B%20I%20then%20get%20the%20below%20error%20-%20it%20seems%20to%20try%20over%20and%20over%20but%20can't%20get%20past%20this%20bit.%26nbsp%3B%20Any%20ideas%20please%3F%20Thankyou%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGet-AzDataFactoryV2PipelineRun%3A%20%2Fhome%2Fanthony%2FE2ESynapseDemo%2FdeploySynapseStorageADF.ps1%3A219%3CBR%20%2F%3ELine%20%7C%3CBR%20%2F%3E219%20%7C%20%E2%80%A6%20ceGroupName%20-DataFactoryName%20%24DataFactoryName%20-PipelineRunId%20%24element%3CBR%20%2F%3E%7C%20~~~~~~~~%3CBR%20%2F%3E%7C%20Cannot%20validate%20argument%20on%20parameter%20'PipelineRunId'.%20The%20argument%20is%20null%20or%20empty.%20Provide%20an%20argument%20that%20is%20not%20null%20or%20empty%2C%20and%20then%20try%20the%3CBR%20%2F%3E%7C%20command%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

For many people, hands-on experience is often the best way to learn and evaluate data tools. I've been working with a colleague from our Azure team, Kunal Jain, to put together an end-to-end Azure Synapse and Power BI solution using 120+ million rows of real CMS Medicare Part D Data that is available for use in the public domain. If you're not highly technical, and you've never used Azure or Power BI before, you can still deploy this solution with a few simple steps using an Azure ARM template. We also provide a video to walk you through a paint-by-numbers tutorial. The Azure ARM template will automatically:

  • Create Azure Data Lake, Azure Data Factory, and Azure Synapse
  • Pull the raw data from CMS into a Data Lake using Azure Data Factory
  • Shape the data and create a dimensional model for Azure Synapse
  • Deploy the solution to Azure Synapse, including performance tuning settings

The entire process takes about an hour and a half to run, with most of that time spent on waiting for the scripts from the ARM template to finish. Once deployed, another video also walks you through the steps of connecting it to a pre-built Power BI report template. The whole process should take about 1-2 hours with no code required, and at the end you can review and evaluate and end-to-end Azure and Power BI solution using real CMS Medicare Part D Data:

Here is a link to the GitHub site: https://github.com/kunal333/E2ESynapseDemo 

 

While the source CMS data is real public Healthcare data, the intent of this project is to provide you with a simple end-to-end solution for the purposes of learning, demos, and tool evaluation. We intend to enhance and build upon this solution in the future, but it is not a supported solution intended to be used for production purposes. 

 

Below is a tutorial video for deploying the solution. Note that this is designed to be low code with only a few things to cut and paste. All you need is an Azure account, and you can pause the Synapse instance or delete the entire Resource Group at any time. There are also simple instructions on the GitHub page:

 

Here is another tutorial video describing the process by which to connect the Power BI Template file containing the business logic:

 

The following diagram summarizes the steps of the whole process:

Source to Target.png

Azure Data Factory, along with Power BI, creates the following logical model that enables highly performant end user queries for complicated questions about the data. Notice that a CSV file is also added to the Power BI layer to demonstrate that custom criteria from a business user can be used to query the Synapse data:

Logical Model.png

 Calculations have been added to the Power BI Semantic Layer to enable complex analytics such as Pareto Analysis: 

Calculations.png

 Below is a screenshot of the pre-built Power BI report:

Dashboard Image.png

 

More information about the solution is available at the GitHub site: https://github.com/kunal333/E2ESynapseDemo 

 

If you deploy this solution, we'd appreciate if you could take the time to provide some feedback. What was your experience with the ARM template? How do you plan to use this solution? What types of similar solutions can we provide in the future that would be valuable?:

 

 

1 Comment
Occasional Visitor

Hello.

 

I get as far as running the deploySynapseStorageADF.ps1 script  step 13/15 I think line 219.  I then get the below error - it seems to try over and over but can't get past this bit.  Any ideas please? Thankyou

 

Get-AzDataFactoryV2PipelineRun: /home/anthony/E2ESynapseDemo/deploySynapseStorageADF.ps1:219
Line |
219 | … ceGroupName -DataFactoryName $DataFactoryName -PipelineRunId $element
| ~~~~~~~~
| Cannot validate argument on parameter 'PipelineRunId'. The argument is null or empty. Provide an argument that is not null or empty, and then try the
| command again.