Get Error log for child pipelines in an execute pipeline activity in ADF

Scenario: We have a pipeline in Azure Data Factory, where we have a few lookup activities and then sequentially execute another  child pipeline using the execute pipeline activity. see the flow below,

The screenshot below is the parent pipeline, where you have two lookups and a sequential execute pipeline step which calls another child pipeline.

See below the child pipeline where you have lookup and a ForEach1 loop,

The problem here is ,when we execute the parent pipeline and something fails in say the ForEach loop in the child pipeline, ADF doesn't call out the actual error from child pipeline in the parent pipeline and only gives out the following error messages, which is very generic.

Now if you see the error above it is very generic and says something failed in the For Each loop in the child pipeline. 

Solution: The solution is simple, just to go the execute pipeline error step, click on output as shown below.

When you click on output, the below popup box appears, click on  pipelineRunid which takes you to the child pipelin.e

Once inside the child pipeline, click anywhere in the workspace and go to output tab, you should be able to see the failure logs as below.

Hope this helped you. 

Difference between AS-WAS and AS-IS reporting in a Data Warehouse

Every DWH/ETL developer should be well versed about this concept, I was a couple of days back explaining the same to a person new in our team. That's when it struck me, why not a post on the same, so here goes. I will try to explain the same, giving an example.

Imagine a customer making monthly payments to a bank loan each month from January 2020 till May 2020, where at the end of the month of March, he changed his Address, he moved to a new county/district. This information will be captured in Customer dimension in EDW as below,

So you have your first customer record, which expires on 31st March, and a new record,which is currently active from 1st April 2020 till high end date.

Now imagine we have a transaction table as below with the 5 payment transactions as discussed before from January 2020 till May 2020, so customer has made 3 payments at his first address and the rest 2 at his new address.

Let us discuss now the AS-IS and AS-WAS concepts with this dataset, most EDW/Reporting systems use "AS-WAS" concept for grouping, as per this, suppose you are grouping the payment amount based on DISTRICT column in "AS-WAS", the result set would look as below,

So as you can infer from the result set above, in AS-WAS grouping, the snapshot as of Transaction date gets reported, i.e when T1, T2 and T3 payments were made, the customer was at Trivandrum, and later when T4 and T5 payments were made, he moved to Cochin.

However in "AS-IS" reporting, all payments are reported against the current active record of the customer, hence the full amount will be reported against Cochin in this case, see result set below to understand,

In AS-IS reporting, the measures reported against dimension attributes as of current date.

Key Concepts and Focus Areas to Clear Azure AZ900 examination

In our last blog post on Microsoft Azure AZ-900 fundamentals certification, We had given a set of tips to quickly and easily clear the exam, only if you are running short on time. We thought again and it would be better if we could again prioritize and again give a subset of topics to be learned, where most questions usually come from. 

Personal recommendation is to go through this 3 hour AZ900 video course from FreeCodeCamp and ExamPro and then do practice tests. Separate blog post available on the same.

If you have already started looking into the course syllabus, you would know that the exam is divided into four sub divisions,

1. Understanding of Cloud Concepts.
2. Understanding of Core Azure Services.
3. Understanding of Security, Privacy, Compliance and Trust.
4. Understanding of Azure Service Level Agreements, Pricing and Life Cycle.

For the first section of Cloud Concepts, below are the key concepts you should learn,

1. Differentiate Iaas, Paas and SaaS. Learn examples for each in Azure. 
2. Classify common resources( Virtual Machine, Azure SQL server, Web Apps) to IaaS, PaaS, SaaS etc.
3. Difference between Capital Expenditure and Operational Expenditure.
4. Differentiate and understating of Dynamic Scalability, Fault Tolerance, Disaster Recovery, Elasticity and Low Latency.
5. Differentiate between Private, Public and Hybrid Cloud ?
6. Understanding of Regions, Availability Sets and High Availability in Azure.
7. Basic understanding of High Availability Regions and how it works ? 
8. Understanding of Resources and Resource Group. 

When move on to the next section of the exam, which is on Azure Core Services, below are the most crucial topics.

1. Basic understanding of the following Azure Core Services. Easy Summary read here.
1. Azure Machine Learning Studio. - Predictive Analytics package.
2. Azure IOT Hub. - IOT device communication services.
3. Azure Congnitive Services - Speech to text, image processing, NLP services.
4. Azure HDInsights  - Opensource Apache Hadoop platform.
5. Azure SQL Server - Relational Database Service.
6. Azure Cosmos DB - NoSQL Database service in Azure.
7. Azure Synapse Analytics - Datawarehouse optimized database solution.
8. Azure Databricks - Apache Spark based analytics service.
9. Azure Functions - Serverless compute services to host web apps.
10. Azure App Service - Service to host web apps.
11. Azure Application insights - Insight app, showing anomalies in web apps.
2. Difference between Compute Services and Server-less Compute Services ? and examples.
3. Understanding of Different Interfaces to create resources in Azure ? CLI, Azure Portal, PowerShell.
4. Using Azure Interfaces to create resources from Windows, Linux, MAC and Android, compatibility of each interface to OS ?
• Windows - CLI, PowerShell, Portal
• Linux - CLI, Portal.
• MAC - Poweshell, Portal.
• Iphone - Portal and CLI.
5. Understanding of Azure Resource Manager ?
6. Understanding of Scale Sets in Azure and limitations of same ?
7. Implementing high availability in Azure ? Minimum configuration required ?
• 2 VMs and 2 availability zones
8. Concept of Virtual Networks, Subnets, Network Security Groups in Azure.
9. Understanding Azure Load Balancer, Azure VPN/Local Gateways and Azure Firewall ?
10. What is Azure Monitor and it's responsibilities?
11. What is Azure Advisor and it's responsibilities?
12. Differentiating Azure Monitor and Azure Advisor responsibilities. 
13. Types of Storage Services in Azure ? Different scenarios of usage.

Below are the main topics under security, privacy, compliance and trust that one needs to read on,

1. What is Azure Active Directory ? 
2. Azure Active Directory integration with On-premise Active Directory ?
3. Multi-factor Authentication in Azure?
4. Azure Security Center ?
5. Azure Information Protection Service in Azure ? ( Protects emails and documents)
6. Setting Locks for Resources in Azure ? Multiple locks on same resources ?
7. Understanding of policies like GDPR, Government, NIST, ISO standards etc.
8. Understanding of setting custom policies in Azure ?
9. Azure Key Vault and it's significance ?
10. Azure DDoS protection services, difference between Basic and Standard schemes ?
11. Azure Role Based Access Control?
12. Azure Compliance Manager ?

The final section is on Pricing, SLA and Lifecycle of Products, the key concepts to look here are below,

1. Azure Subscriptions and Azure Management Groups ? Hierarchy of Subscriptions/Management Groups, Can Subscriptions be clubbed ? Can resources be moved from one subscription to another ?
2. Subscription management understanding ?
3. Difference between Public Preview, Private Preview and General Availability ?
4. TCO calculator understanding.
5. Understanding of SLA in Azure ? Percentage of uptime? 
6. Combined Composite SLA's when combining two Azure Products ?
7. How to improve SLA's by going for two region deployment, high availability etc.
8. Understanding of how Azure pays you back when SLA is not met ?
9. Azure Support plans available, differentiate Basic, Developer, Standard, Professional Direct and Enterprise Support.

Note: The questions and topics discussed above are the key focus areas of AZ900 exams, however you should run through the other sections of the exams to gain confidence. 

BODS - SQL Server VARCHAR(Max) source field to target VARCHAR [SOLVED]

Scenario: We had this scenario, where the SQL server database column in our source table was created as VARCHAR(MAX), in order to accommodate large descriptions. This field is to be stored into a VARCHAR(65000) field on the target table. 

Issue: When importing this VARCHAR(MAX) source field, since BODS doesn't have the same datatype defined, this gets imported as LONG datatype in BODS. When you try to map this LONG filed to target VARCHAR field, there is a conversion error that comes up. 

Resolution: The resolution to this problem is to convert the LONG datatype identified by BODS to a VARCHAR field. The CAST. and other commonly used Conversion functions are not capable enough to handle this scenario. We referred to the BODS designer documentation and found that there are two functions to convert Long datatypes to Varchar and vice-versa, 

• LONG_TO_VARCHAR(TableName.ColumnName, IndexPosition, Length) - Converts Long datatype to Varchar. 

             The function expects three parameters, 

1. TableName.ColumnName, suppose your table is STUDENTS and column name is REMARKS, This part of the function will be ' STUDENTS.REMARKS '.
2. Maximum Size - Second argument is the maximum size of the long column to be considered for conversion.
3. Starting Position(Optional argument) - The third argument would be the Index position to start from for the conversion.

Screenshot from Reference Guide,

        

        Example - 

            LONG_TO_VARCHAR( <column_name>, <max_size>, <start_position> )

            LONG_TO_VARCHAR( STUDENT.REMARKS, 10000, 1) 

        The above function says, convert STUDENT.REMARKS field into a varchar datatype from Long, starting from index position 1 and go up till string index position of 10000. This converted field can now be stored into a VARCHAR field in transform/target.

How I cleared AZ900 - Approach, Materials and Mock/Practice exam - June 2020

So finally I cleared my AZ 900 examination today and got my first Azure Badge. It was an online exam scheduled at 7 15 PM IST. I joined online at around 6 30 PM and cleared my test prerequisites checks, around 6 45 the 'Begin Exam' link started appearing.  

A proctor(invigilator) will join you on chat and verify your governemnent ID details and the surroundings of your test room. Once they are convinced that you are a fair candidate, They initiate the exam. 

Coming to the exam,  I had a total of 32 questions, and was fairly easy considering I prepared just a couple of days ahead. Below are the materials that I used to prepare, 

• FreeCodeCamp.Org crash course in Youtube - Partnered with ExamPro.com

This is the only video tutorial I went through to crack the exam. It is just 3 hours long, really easy and straight forward. Doesn't overburden you with a lot of things and is straight to point. 

• Practice questions in Udemy Course - Microsoft Azure - Beginner's Guide + AZ-900 - UPDATED 2020

        This is a very exhaustive course. If you really have the time, do consider giving this course a try. More than the course, there are two practice set of questions at the end of this course. First one with around 115 questions and second with 100 questions. The questions I got on the exam were mostly connected to the questions on this course. Maybe a little re-worded from the course, but the content sticks to the same. I did both these question sets around 2 to 3 times on exam day and made sure I consistently got 90%+ scores. 

• Examtopics AZ900 exam questions dump - https://www.examtopics.com/exams/microsoft/az-900/view/

       I also went through this dump, this one is really close to the exam as well. You can expect most questions in this exam to be more or less connected to the questions discussed here. 

Out of the 32 questions, guess around 4-5 were not from the dump, but were more or less connected to questions from these dumps.

Update - 06/29/2020 - Suggested read on Key Focus Areas and Concepts to Clear AZ900 exam

To summarize, if you are running short on time. Start with the 3 hour FreeCodeCamp course. Thoroughly take notes and understand. Once you are done with the course, repeat, repeat, and again repeat these practice questions until you consistently get a 90%+ score.

Resources in Microsoft Azure - Hierarchy, Prerequisite and Examples

Resources are the most fundamental entity in a Microsoft Azure cloud solution. Anything from Virtual Machines to Azure SQL databases to Storage accounts can be classified as an Azure resource. 

To create a resource in Azure, as a prerequisite, you would require four levels of hierarchy, 'Resource' being the last in the tree and Management group being the first. The below diagram explains a sample, where we examine the resources created for a sample ETL project. Where you have two database servers, one for source and target, a resource each for both. Since they are both resources that point to databases, logically I grouped them under one Resource Group. 

Here in the example below, we created one resource group for the server hosting the ETL tool. The images of this server for multiple environments( Development, Test, Production), all can be logically grouped under one single Resource Group again. Same way for any visualization tool. 

The first and fore-most prerequisite to create a resource is to have a Resource group. Again a resource group is only created under a Subscription group and a subscription group under a Management group.

Below are the four levels,

1. Management Group - Logical containers holding multiple subscriptions
2. Subscription - Logical containers holding Resource Groups and billing in Azure.
3. Resource Group - Logical containers grouping multiple individual resource, separately or individually.
4. Resources - Lowest level of entity/object in Azure.                       

More reads on Azure Resource and it's hierarchy are available in below link.

• https://docs.microsoft.com/en-us/azure/cloud-adoption-framework/ready/azure-setup-guide/organize-resources?tabs=AzureManagementGroupsAndHierarchy

Creating your First Azure Cloud Free Account - Step by Step

  

Recently I tried out creating my first Azure free account to play around the portal and prepare for the 'AZ-900' fundamentals certification. Below are the steps to create your own, it's really easy, however you require a few prerequisites upfront.

Prerequisites to create an Azure free account,

1. A valid working Phone number.
2. A valid Credit/Debit Card.
3. A Microsoft Account username. 

First you need a Microsoft Account, I already had one as I use a secondary outlook account. If you don't have one it is really easy to create, you can create one here at https://account.microsoft.com/account?lang=en-hk .

Secondly you need to have a valid Credit/Debit Card, This is mandatory, I was charged Rs 2, when I signed up. Other than this nominal fee, Microsoft doesn't charge you unless you allow it to. If you exhaust up your resources, you will be asked if you need an upgrade, if you opt for one, you will be charged based on the plan you opted for. So for people looking to create a new account, this shouldn't be a worry/deterrent. 

Thirdly, you need to have a valid phone number which is associated to your Microsoft account, this is where you receive a One Time Password(OTP) which you have to provide during the sign-up process. 

Once you have the above prerequisites ready, go to  https://azure.microsoft.com/en-us/free/, click on Start Free,

This will take you to a new form page, where you have to give details across four sub sections, 

About you, Identity verification by Phone, Verification by Card, and Agreement.

About you

In About you section, basic details are requested for, from First Name, Last Name, Email Address, Phone Number and Organization. See screenshot below,

Here PAN ID is optional.

Verification by Phone,

In the second section, you have to verify your mobile number with a one-time-password (OTP) send to your mobile number. 

Identity Verification by Card

This is where you have verify your identity, by providing your Debit/Credit card details.

I was charged a very nominal Rs 2, while creating the account.

Sign Agreeement

That's it, once you are done. You can login to your free account and start exploring. Happy learning everyone. 

AZ 900 - Microsoft Azure Cloud - Basic learning path - Part 1

Updated - 06/26 - I cleared AZ900 and here is my AZ900 quick learning path. 

For my career, right now it's high time I up-skilled and get certified in Cloud. I currently work on an Insurance framework which is hosted on an AWS Cloud platform. Though my current work confines me inside an AWS VM machine at all times, I understand that the world is moving aggressively towards cloud platforms and it is easy to fall behind if you don't keep up with that pace.

Thankfully a bunch of colleagues have started a group team learning session to share knowledge around Cloud with the aim of getting the team certified. 

The plan is to get myself certified on the below, before the end of the year, more than getting certified, get a detailed understanding of how cloud works. 

• AZ-900 - Microsoft Azure Fundamentals
• DP-200 - Implementing an Azure Data Solution

In Parallel, I have also started at looking multiple materials on the Azure Cloud. Here are a few materials online, which helped me get a basic understanding of Cloud for AZ-900 certification.

Microsoft Azure for Enterprises: What and Why ? - PluralSight

           Duration : 1 hour 13 minutes. 

           Author: David Chappel

            I was very fortunate to come across this course in April 2020, when PluralSight really opened up it's courses for Free. The course was published in 2015, despite that the course covers Cloud computing basics, and it explains a wide range of scenarios of how Cloud can be implemented by Enterprises and how it can add significant value over the in house systems.

The author is a very experienced cloud architect/practitioner, who at all points in the course, keeps it simple and straight forward. There is no overuse of technical jargon. Easy to understand scenario examples.

Each time I learn something new, I keep a bookmark to a 'Back to the basics course', and this would definitely be the one for Cloud Computing. 

Microsoft Documents Library for getting started with Azure.       

         Going through official documentation is not always a pleasant and fruitful experience. Some tools/frameworks have such complex documentation that you end up being more puzzled than before you referred it. This case it is really simple and straight forward. I already completed two units and it's really easy. My suggestion is you take up a video course and in parallel go through this Microsoft documentation.

I am also looking at finding and shortlisting one Udemy course, there are a handful of ones with good review and rating.  I am trying to shortlist one and write note in the Part 2 version of this blog series - Learning Cloud.

There is also a plan for me to make this a series, I will try to prepare a cheat-sheet notebook and try sharing it here.

Parameterize Schema/Owner Name in SAP BODS SQL scripting function

Scenario: Requirement was to parameterize a schema name in SQL function in BODS scripting language. Schema name should be fetched from the datastore connection and used dynamically in job.

 You can follow the below steps to get this done,

Step 1: First fetch the schema name from the datastore name. Declare a variable in workflow, say $LV_TableOwnerDB to store the schema name. WE can use db_owner pre-built function for the same as below,

# Get the schema/owner name from from datastore using alias name.

$LV_TableOwnerDB = db_owner( 'DATASTORE_NAME', 'ALIAS_NAME');

 print('TABLE OWNER  = '||$LV_TableOwnerDB);

Now $LV_TableOwnerDB variable would store the schema name of your target database.

Step 2: Now declare another variable where you store the SQL Script to be executed.

$LV_Temp = 'UPDATE '||CAST($LV_TableOwnerDB,'VARCHAR(15)')||'.TABLE_NAME SET ACTIVE_FL =\'Y\' WHERE ACTIVE_FL=\'N\'';

Print($LV_Temp);

Step 3: Now pass the variable with SQL stored as argument to the sql function as the second parameter below.

sql('DATASTORE_NAME',$LV_Temp);

Suppose the Datatstore name is 'EDW_HR' alias name defined in datastore is 'ALIAS_1', the script would be as below,

# Get the schema/owner name from from datastore using alias name.

$LV_TableOwnerDB = db_owner( 'EDW_HR', 'ALIAS_1'); print('TABLE OWNER  = '||$LV_TableOwnerDB);
$LV_Temp = 'UPDATE '||CAST($LV_TableOwnerDB,'VARCHAR(15)')||'.TABLE_NAME SET ACTIVE_FL =\'Y\' WHERE ACTIVE_FL=\'N\'';Print($LV_Temp);
sql('EDW_HR',$LV_Temp);

Let us know if this helped. :)