• ADF Data flow string split

    October 28th, 2022 in Blog, Data Factory, Synapse

    This is a quick blog showing how to do a string split to get particular items in ADF data flows. Consider the following data where names and colours are combined into the FullName and Colours columns respectively. Note the delimiter for FullName is a space and the delimiter for Colours is a comma. To get each individual item and create new columns for this data use the split function in a Derived column transformation. The syntax for this…

    » Read more

  • ADF breaking out of a ForEach activity

    July 14th, 2022 in Azure, Blog, Data Factory, Synapse

    Currently, the ForEach activity in ADF (or Synapse) does not break out when an inner activity fails. There are several ways you can force the ForEach to break out, the most common is to cancel the pipeline run when an inner activity fails and there are already many blogs out there that cover this. My requirement is slightly different and that is what I will show here. Consider a scenario where you have a ForEach activity which has multiple inner…

    » Read more

  • ADF Dataflow CTE workaround

    May 13th, 2022 in Azure, Blog, Data Factory, Synapse

    At the time of writing, it is not possible to write a query using a CTE in the source of a dataflow. However, there are a few options to deal with this limitation: re-write the query using subqueries instead of CTEs use a stored procedure that contains the query and reference the stored proc in the source of the dataflow write the query as a view and reference the view in the source of the dataflow (this is my preferred method and the one I will…

    » Read more

  • Execute SQL Task in ADF

    February 25th, 2022 in Azure, Blog, Data Factory, SQL Server, SSIS, Synapse

    If you’re new to ADF or Synapse pipelines and looking for a way to execute SQL commands in pipelines, unfortunately there is no equivalent to the Execute SQL Task in SSIS, but I have found some alternatives which work well for me. For something as simple as a SELECT statement where you want to return results to be used later, you can use a Lookup activity: You can then reference the output of this activity in other activities using dynamic…

    » Read more

  • Azure Storage Backup Retention

    November 22nd, 2021 in Analysis Services, Azure, Blog, PowerShell

    This blog is a follow up to a previous blog I wrote about backing up Azure Analysis Services cubes in Azure, that blog can be found here. This blog shows how to implement a retention policy using PowerShell in Azure Runbooks to remove the backups after a set number of days. To create a new Runbook in the Azure portal, go to the relevant Automation account in the relevant resource group and then select Runbooks from the left hand pane. Note you…

    » Read more

  • Variables in DAX

    September 10th, 2021 in Analysis Services, Blog, DAX, Power BI

    Variables can simplify your DAX code, help with debugging and help improve performance. To use variables in your DAX measure you need to declare a variable using the VAR keyword, give the variable a name, and then assign an expression to the variable. You can use multiple variables in a measure but when using variables you must use a RETURN statement to specify the final output to be returned. To show how to make use of variables I’ll go…

    » Read more

  • My DAX Training Evaluation

    April 30th, 2021 in Blog, DAX

    Being fairly new to DAX and having recently completed some DAX training I thought it would be good to briefly share my experiences of the courses I have completed. The two courses I have recently done are: Stairway to DAX and Power BI by Bill Pearson (https://www.sqlservercentral.com/stairways/stairway-to-dax-and-power-bi) Mastering DAX by Marco Russo and Alberto Ferrari (https://www.sqlbi.com/p/mastering-dax-video-course/) Stairway to DAX is…

    » Read more

  • Azure Analysis Services Cube Backup

    December 23rd, 2020 in Analysis Services, Azure, Blog, PowerShell

    This blog is a quick guide on how to back up an Azure Analysis Services cube using PowerShell. The PowerShell is used in a runbook inside an Automation account in Azure to automate the backup process. A pre-requisite for this is to configure the backup storage settings, this details the location of the backups. Two parameters are passed into the runbook (shown below), where AnalysisServerName is the server name for the analysis services cube and…

    » Read more

  • My experience in obtaining an MCSE

    August 7th, 2020 in Analysis Services, Blog, Business Intelligence, Integration Services, SQL Server

    Background I have recently completed my MCSE in Data Management and Analytics, and I wanted to share my experience of working towards and passing the exams that have led to me getting this MCSE. In this post I will cover some of the challenges I faced and provide some tips and advice, hopefully others following the same certification path, or other paths, will find this useful. I am a business intelligence developer at Purple Frog, I have…

    » Read more

  • SSAS Tabular Calculation Groups – avoid using SELECTEDMEASURE or ISSELECTEDMEASURE

    June 17th, 2020 in Analysis Services, Blog, Power BI, Tabular

    Introduction: There is a very serious limitation in the behaviour of calculation groups when using the SELECTEDMEASURE or ISSELECTEDMEASURE functions, and we recommend not using them. Why? If a user creates their own custom calculations within their Power BI report (or composite model) then the value of SELECTEDMEASURE changes, breaking your calculation group logic. Let me explain with an example: In a tabular cube we may look to use calculation…

    » Read more