Introduction to Azure Integration Services

Do you want to learn how to leverage and integrate disconnected business systems? In a recent webinar, 3Cloud’s Application Modernization Practice Director, Nick Althoff, teaches you what Azure Integration Services is and how you can leverage it to integrate disconnected business systems.

Azure Integration Services is Microsoft’s cloud offering that consists of a set of serverless Platform as a Service (PaaS) components that can be used together to do this integration. For those that are unfamiliar, when you are running something serverless you are leveraging compute (RAM and processor) that Microsoft is responsible for maintaining, no spinning up VMs or installing an operating system. Plus, you’ll benefit from event-driven scale to scale your workloads effortlessly, as well as save money with sub-second billing.

The bulk of this presentation digs into not only an overview of Azure Integration Services but into each of the components that make up Integration Service: API Management, Logic Apps, Event Grid, Service Bus, Function Apps and Event Hub. You’ll come away with a sense of how these components fit in the Azure Integration Services platform.

So, if you’re looking to integrate your disconnected business systems using Azure Integration Services, this webinar is for you. You can watch the complete recorded webinar below.

Be sure to join us each week for our free webinars that happen every Tuesday at 11 AM ET. Our webinars cover a wide range of Azure topics and are presented by industry experts. Check out what webinars are coming up on our events calendar.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

3CloudIntroduction to Azure Integration Services
Read More

Daylight Savings Time Changes in Power Query

In this post, I’ll show how to accommodate for Daylight Savings Time changes in a Power BI file. The video I’ve included will show the formulas that I use and how they adapted from Daylight Savings Time to Standard Time. There are many ways to do this and it is best to find a way that accommodates what you need to do, but I’ll walk through how I do it for my needs within Power Query.

  • The first thing I do in Power Query is to identify the start and end date of Daylight Savings Time. In the US, the start date is the second Sunday in March and end date was the first Sunday in November and I’ve added the time of 2:00am.
  • You could look these up and make a list of them for the next 8 or 10 years but to do it formulaically, I use a DateTime.LocalNow and Date.Year to pull the year out of whatever year we are in.
  • In the Applied Steps area in my query settings, I create a small temporary calendar table using that the year that will start in March and end in November. I create a list of those dates under source and then I convert that list into the actual dates.
  • No I have a calendar table with column1 being all dates listed from the start of March thru November. Using the Added Custom fields in Applied Steps, I add a column for month number and one for number of the day of the week (0-6).
  • Since I’m only interested in Sundays in March and November, I filter this table to show me only Sundays (day of week 0) in those months. More specifically, I need to know the second Sunday in March and the first Sunday in November. To do that, I want to group rows by their month number and then add an index column.
  • If I click on one of the columns in my table, I can see a Sunday index which is counting in order (1st, 2nd, 3rd Sunday). If I remove the other columns and just keep the one with the index and expand it, I see I have all the information pieces I need (month number, Sunday index columns).
  • Next, I’ll add a little helper column (GetDays) that will combine the month number and Sunday index columns together. I filter that helper column for the 2nd Sunday in March and the 1st Sunday in November.
  • So, I have all I need but I just need to add a time, so I’ll put a time component in and combine it with the date, so I have a DateTime column.
  • It’s important to note that when we do comparisons in Power Query between dates, date times or date time zone, you want to be consistent with your format. If I’m going to compare date time zones, I need to put my date times into a date time zone. I also need to ensure that the time zone is the same, so I’m going to use a consistent switch. I’ll add a time zone to my start and end dates.
  • I’ve added two queries (StartTime and EndTime) in which I’ll reference that DateTime list. In my formulas, I’ll write in the DateTime in March as the min and the end DateTime in November as the max. I’ll also need to add the time zone to each and make sure I’m consistent. Check out my video for more detail of the formulas.
  • The logic here is an If/Then statement. If the switch time zone which I’ve set to -4 (which is Eastern Time during Daylight Savings), is greater than the start time and less than the end time, then we are in Daylight Savings and the time is correct.
  • If it’s not in between those two dates, then we’re not in Daylight Savings and we need to subtract an hour from it because now we’re going to fall back. In other words, we’re going to be 5 hours behind instead of four. If I refresh it again, it should match the time that’s on my computer. In my SwitchTime Zone column, I see the time that it was in the summer and in my DateTime with DST Correction column I see the correct time now which matches my computer, so I know that this is working correctly.
  • But how do I know it’s working to the exact second? I’ll just duplicate my three queries and create a testing query. In this testing query I will arbitrarily set the start and end time to be a specific day and time to check back on my table to see if it matches. This testing helps ensure that it’s working perfectly.

My video below goes into more detail and may make this written demo clearer. I hope my peek into how I can accommodate for Daylight Savings within Power Query is helpful. This is certainly not the only way to do this, but it works well for me, so I thought it was worth sharing.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected]

Steve WiseDaylight Savings Time Changes in Power Query
Read More

Azure Data Factory 101

Are you just starting out with Azure Data Factory? In this post, I’ll give you an introduction to Azure Data Factory, covering what it is, how it works and how to set it up. Within the video included this post is a short demo of how to create and access the platform.

What is Data Factory?

  • Here is a clear definition that I found from the Cloud Academy. Azure Data Factory is a cloud-based data integration service that allows you to create data-driven workflows in the cloud for orchestrating and automating data movement and data transformation.

How Data Factory works?

  • The key components of Data Factory are pipelines, activities, and datasets.
  • Pipelines are made up of activities. There are 3 types of activities:
    • Movement – the copy activity
    • Transformation – including Azure Functions, HD Insight, Stored Procedures, Spark and Databricks
    • Control – ForEachLoops, If Condition Branching, Wait Times, and Validations
  • Datasets represent the inputs and the outputs of the activities.
  • Linked Services – these are the connection strings and authentication for all types of sources for the data sets.
  • Data Flows – are the results of the datasets where you can apply logic and transform the data.
    • Mapping Data Flows are graphical with drag and drop functionality.
    • Wrangling Data Flows are more like using Power Query or M.
  • Integration Runtime – allows you to do data integration across different network environments. There are three types of runtimes: Azure, Self-hosted, and Azure SSIS. Depending on where the data is that you need to copy will determine which of these is appropriate for the use case.

In the video below, I provide a brief walk through of how to access and create in Azure Data Factory. Please check it out, as I think it is a good resource for those just starting out.

Our Azure Every Day series is another great resource. 3Cloud consultants posts weekly blogs and videos around all things Azure. You’ll find all current and past blogs on our website or by clicking here.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

Leslie AndrewsAzure Data Factory 101
Read More

Tabular Modeling & Calculation Techniques Beyond the Basics in Power BI

Do you want to learn how to address real-world issues with Power BI and DAX? In a recent webinar, Principal Consultant, Paul Turley, teaches you what you need to know. Paul covers many-to-many relationships, using disconnected tables and takes a look at using composite models.

This presentation takes a deep dive into Tabular modeling and calculation and includes demos. Paul’s agenda covers:

  • Dimensional modeling basics (just a quick run through as this is a more advanced presentation)
  • Filter propagation in Tabular models
  • Effects of single and bi-directional relationships as you are creating relationships in your data model
  • A look at scenarios where data model relationships can or cannot be used to achieve requirements or may or may not solve business problems
  • Dynamic measures built on a dimensional data model
  • A demo on DirectQuery in a Gen 1 composite model with Import mode
  • A first look at new Gen 2 DirectQuery using Power BI datasets which is the next generation of composite model capabilities which is now in public preview

All these topics and demos are based on a business requirement or business problem context, so the presenter will set up a business problem and work through how it can be solved with dimensional modeling.

So, if you’re looking to learn about using Power BI and DAX to address real-world business issues, then this webinar is for you. You can watch the complete webinar below.

Be sure to join us each week for our free webinars that happen every Tuesday at 11 AM ET. Our webinars cover a wide range of Azure topics and are presented by industry experts. Check out what webinars are coming up on our events calendar.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

3CloudTabular Modeling & Calculation Techniques Beyond the Basics in Power BI
Read More

A Few Tips When Creating Power BI Mobile Reports

Power BI is the best BI and reporting tool out there, and so is Power BI Mobile. Microsoft has made mobile access a first-class experience in the Power BI platform. All the heavy lifting like authentication, data security and network connectivity is all automatically delivered out of the box.

Just download the app and sign in once, then sit back and enjoy access to all your reports and dashboards. You’ll also get integration with your mobile device’s virtual assistant, so you can ask Siri for the sales report and she’ll pull it up for you to view.

Awesome stuff, right? But as a developer, you may not know how to deliver an optimized view of your analysis for mobile consumption. Here, I’ll walk you through creating a mobile version of your report. I’ll point out the major features, as well as some valuable tips and tricks.

  • In the Power BI Desktop view, you can look at the design for the mobile view by clicking on View and then Mobile Layout. This gives me a blank canvas and on the right side is a visualizations pallet, which at this point only lists the visualizations from my desktop.
  • I can pull over one of my desktop visuals and add my report title and company logo, but honestly, it’s not that great. I want to change the way it looks or even create a totally different visual just for my mobile layout, but unfortunately, I’m stuck with the visuals that I have on my desktop.
  • To do this, here’s my first big tip for a work around; you’ll need to create visuals, put them on your desktop version, and then hide them. Let’s walk through this:
    • The title bar ‘Sales Analysis’ on my mobile view is huge so I want to make it smaller. I go back to my desktop and make a copy of my title bar and make one with a smaller font size.
    • Next, I go over to my mobile layout, get rid of the large one and add the smaller one.
    • Here’s the problem, when I go back to the desktop, I want to get rid of that smaller font title text box and when I delete it, it will disappear from the mobile view as well.
    • If I try to hide it in the desktop view, it will also hide it in my mobile view.
    • So, my trick is to hide that text box with the smaller font behind a larger text box in my desktop view. All I need to do is lineup that mobile only text box on top of the other one, go to Format and click Send to the Back. You can also do this in the Selection panel on the right side and drag the text box to the bottom of the list, and it will put it behind everything else higher on the list.
  • Next, I want to create a visual that is more meaningful in my mobile view. In my demo, I create a copy of my bar chart visual and I filter it to show only the most recent 3 months of my data.
  • I’ll shrink that down a bit and hide it behind the larger bar chart on my desktop. That new 3-month bar chart will appear in my mobile visualizations pallet and I can pull that over onto the canvas.
  • When I save this and publish it, I’ve created one version that users who view it from the desktop will get the desktop view. Users that view it from their phone will see the desktop view if they are in horizontal mode but will get the mobile view if they are in vertical mode.

When I’m on my phone and open the Power BI app, I see two different icons. The mobile version I created and published has a different icon with a phone on it. This way end users can quickly identify the version that is intended for the mobile use. I can rotate my phone horizontally and see the desktop version, but the goal of creating a mobile view of reports is to just have distinct data (in my case, the most recent 3 months) available for a quick update.

I can also add a quick Siri shortcut by simply telling Siri that when I say, ‘open mobile sales report’, my phone will go right to that mobile report. These tips should be super helpful when creating Power BI mobile reports.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

Chris SilvernailA Few Tips When Creating Power BI Mobile Reports
Read More

Introduction to Azure Automation

Do you want to learn how to automate administrative tasks in your Azure environment? In a recent webinar, we discussed how to automate various processes in Azure using PowerShell, Runbooks, and Automation Modules. When you know these techniques, you’ll be able to do things such as automate the starting and stopping of services, even processing Analysis Services Models!

This demo-heavy presentation will explain and help you understand Azure Automation and Runbooks. It will show you how to create a PowerShell Runbook and discuss options to setup a Runbook. The demo will take you through the steps, including testing and publishing a sample Runbook code and how to run a Runbook job.

So, grab 40 minutes and watch this demo if you want to learn about how to integrate automation within your Azure environment, so you can save time on those routine, administrative tasks and focus on more important things. You can watch the complete webinar below.

Our free webinars happen every Tuesday at 11 am EST and cover a variety of Azure topics that are presented by industry experts and consultants. Be sure to check out our monthly calendar for upcoming webinars.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

3CloudIntroduction to Azure Automation
Read More

Avoiding Issues When Generating Time/Date in Power BI

Have you ever run into hurdles when trying to generate time and date in Power BI? I know I have, so I’m here to help and to demonstrate the differences between generating time and date in Power BI Desktop vs in the Power BI Service.

A few key points before I dig in:

When we generate time and date in Power BI Desktop using the native Power BI tools, it pulls that information from our local computer settings.

In the Power BI Service, the Service generally exists in a different server time and date than your local machine. Therefore, there will be some differences between the times as they appear in the desktop file and how they appear in the Service.

Currently, there is no native functionality to handle Daylight Savings Time changes. Keep this in mind when you start to generate your own time and dates – you’ll need to create tools that take this into account.

What I’ve done will be much easier to see by watching my video instead of trying to explain in text, as it could be confusing. Here’s a brief overview of my video.

  • In my video, you’ll see that I went into Power Query and copied the different queries that I used to generate time and date.
  • The first one is generating time and date in Power Query using DateTImeZone.LocalNow to pull the local time. Remember, when operating in the PBI Desktop, this is pulling information from my local machine.
  • What I’m going to do is reuse some of the formulas and look at how we can switch the time and what those changes look like as I go through the queries I’ve highlighted.
  • In my demo, you’ll see that rather than publishing this file and jumping in the Power BI Service to look at it, I’ve taken the queries that I’ve used to generate my times, put them in the Service as a data flow, and then imported that dataflow into a Power BI Desktop file.
  • So, I have 2 pages; one shows the information from the data flow and the other is the information generated locally. This way I can show side by side what the changes are going to look like.
  • I’ll walk you through the steps of these scenarios and the queries I used:
    • Manually switching time using #duration
    • Modifying date/time to reflect Eastern Standard Time (-4 UTC) using SwitchZone
    • Modifying time/date in Power Query using SwitchZone and FixedLocalNow (time at start of query execution)
    • Adjusting time -4 or -5 based on daylight saving calendar

In summary, this post covers the changes to date/time as we move from Power BI Desktop to the Service and what are the best formulas to use when we are generating our own time. You’ll see that the key takeaway is the best way is to use SwitchZone when adjusting between the Desktop and the Service.

I think you’ll find this helpful when running into date/time hurdles in Power BI.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].

Steve WiseAvoiding Issues When Generating Time/Date in Power BI
Read More

3Cloud Earns Microsoft’s Azure Advanced Specializations in Modernization of Web Applications, Windows Virtual Desktop & Windows Server and SQL Server Migration

3Cloud is proud to announce it has earned Microsoft Azure Advanced Specializations in Modernization of Web Applications, Windows Virtual Desktop and Windows Server and SQL Server Migration to Azure. By earning these advanced specializations, we are able to further differentiate ourselves and validate our deep knowledge, extensive experience, and expertise within these areas of Microsoft Azure. We are thrilled to have earned advanced specializations in the following areas to best meet the needs of our customers:

Modernization of Web Applications: Only partners that meet stringent criteria around customer success and staff skilling, as well as pass a third-party audit of their web workload deployment and management practices, including their ability to implement Azure App Service, are able to earn the Modernization of Web Applications in Microsoft Azure advanced specialization.

Windows Virtual Desktop: This is Microsoft’s newest advanced specialization. Windows Virtual Desktop has increased in demand as organizations across the globe are working remotely and many are transitioning to desktop-as-a-service to enable their employees. Partners must demonstrate extensive knowledge and experience with deploying, optimizing, and securing virtual desktop infrastructure on Azure.

Windows Server and SQL Server Migration to Microsoft Azure: This advanced specialization validates a solution partner’s experience, knowledge, and expertise in migrating Windows Server and SQL Server-based workloads to Azure. Partners must meet strict criteria around staff skilling, customer success and pass a third-party audit of their migration practices.

As companies look to modernize and take full advantage of the benefits of the cloud, they are looking for a partner with advanced skills to migrate, optimize, and manage their environments. 3Cloud continually strives to be that partner in guiding clients on their Azure adoption journey.

Jim Hughes, VP, Solution Architecture at 3Cloud stated, “As we continue our mission in helping our customers achieve The Ultimate Azure Experience, we are excited to announce that 3Cloud has been awarded the Microsoft Windows Virtual Desktop Advanced Specialization. With this award, we became the first US Microsoft Partner to achieve this goal. In addition, 3Cloud has also earned Advanced Specializations in Modernization of Web Applications and Windows Server and SQL Server Migration. I’m excited and proud of these accomplishments and to be part of an incredible team that provides our clients with top-notch guidance in their journey to Azure.”

3Cloud guides clients on their journey to the cloud by providing the Ultimate Azure Experience. Our experience, reputation for excellence, and on-going support define us as a leader in the Azure ecosystem. Please reach out if you have questions about any of our solution offerings or would like to discuss how we help your business at every stage of your Azure journey at [email protected] or call (888) 88-AZURE. Learn more about Microsoft Azure Advanced Specializations in the link below.


3Cloud3Cloud Earns Microsoft’s Azure Advanced Specializations in Modernization of Web Applications, Windows Virtual Desktop & Windows Server and SQL Server Migration
Read More

What is Delta Lake in Databricks?

If you’re not familiar with Delta Lake in Databricks, I’ll cover what you need to know here. Delta Lake is a technology that was developed by the same developers as Apache Spark. It’s designed to bring reliability to your data lakes and provided ACID transactions, scalable metadata handling and unifies streaming and batch data processing.

Let’s begin with some of the challenges of data lakes:

  • Data lakes are notoriously messy as everything gets dumped there. Sometimes, we may not have a rhyme or reason for dumping data there; we may be thinking we’ll need it at some later date.
  • Much of this mess is because your data lake will have a lot of small files and different data types. Because there are many small files that are not compacted, trying to read them in any shape or form is difficult, if not impossible.
  • Data lakes often contain bad data or corrupted data files so you can’t analyze them unless you go back and pretty much start over again.

This is where Delta Lake comes to the rescue! It delivers an open-source storage layer that brings ACID transactions to Apache Spark big data workloads. So, instead of the mess I described above, you have an over layer of your data lake from Delta Lake. Delta Lake provides ACID transactions through a log that is associated with each Delta table created in your data lake. This log records the history of everything that was ever done to that data table or data set, therefore you gain high levels of reliability and stability to your data lake.

Key Features of Delta Lake are:

  • ACID Transactions (Atomicity, Consistency, Isolation, Durability) – With Delta you don’t need to write any code – it’s automatic that transactions are written to the log. This transaction log is the key, and it represents a single source of truth.
  • Scalable Metadata Handling – Handles terabytes or even petabytes of data with ease. Metadata is stored just like data and you can display it using a feature of the syntax called Describe Detail which will describe the detail of all the metadata that is associated with the table. Puts the full force of Spark against your metadata.
  • Unified Batch & Streaming – No longer a need to have separate architectures for reading a stream of data versus a batch of data, so it overcomes limitations of streaming and batch systems. Delta Lake Table is a batch and streaming source and sink. You can do concurrent streaming or batch writes to your table and it all gets logged, so it’s safe and sound in your Delta table.
  • Schema Enforcement – this is what makes Delta strong in this space as it enforces your schemas. If you put a schema on a Delta table and you try to write data to that table that is not conformant with the schema, it will give you an error and not allow you to write that, preventing you from bad writes. The enforcement methodology reads the schema as part of the metadata; it looks at every column, data type, etc. and ensures what you’re writing to the Delta table is the same as what the schema represents of your Delta table – no need to worry about writing bad data to your table.
  • Time Travel (Data Versioning) – you can query an older snapshot of your data, provide data versioning, and roll back or audit data.
  • Upserts and Deletes – these operations are typically hard to do without something like Delta. Delta allows you to do upserts or merges very easily. Merges are like SQL merges into your Delta table and you can merge data from another data frame into your table and do updates, inserts, and deletes. You can also do a regular update or delete of data with a predicate on a table – something that was almost unheard of before Delta.
  • 100% Compatible with Apache Spark

Delta Lake is really a game changer and I hope you educate yourself more and start using it in your organization. You’ll find a great training resource from the Databricks community at:

Or reach out to us at 3Cloud. Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].


Brian CusterWhat is Delta Lake in Databricks?
Read More

Azure Trends: Looking Ahead to 2021

2020 has been many things. We’ve seen massive change and resiliency in our interactions with clients and each other. Working remotely for most organizations has changed the way we think about running our business and what we need to do for the future.
In a recent webinar, our delivery leaders discussed the lessons learned from 2020 and looked forward to 2021 to share what they’re looking for in the new year. From DevOps to modernizing applications to data, all the key areas of technology are discussed. Check out this webinar for an engaging, round robin-style conversation on the technical challenges and opportunities you and your team can expect as we say farewell to 2020.
You can watch the complete webinar below.

3Cloud is committed to bringing you the most up-to-date topics covering all areas of Azure, data, and the cloud in our free weekly webinars. We are filling our calendar with hot topics delivered by industry experts in 2021. Follow us on social media to stay up to date and keep checking our event calendar on our website.

Our free webinars happen every Tuesday at 11 a.m. ET. We look forward to you joining us each week in 2021!

3CloudAzure Trends: Looking Ahead to 2021
Read More