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.