Call today 0845 643 6463

Synapse

  • Linked Server with Unique Identifier Issue

    March 2nd, 2023 in Blog, Linked Server, SQL Server, Synapse

    There is currently an issue with Linked Servers when querying and filtering by a guid (uniqueidentifier). If you query a Synapse SQL pool from your on-prem SQL server, for example, and attempt to filter the results using a uniqueidentifier column, you will get the following error message: Msg 103010, Level 16, State 1, Line 1 Parse error at line: 1, column: 408: Incorrect syntax near ‘guid’. Msg 110811, Level 16, State 1, Line 1…

    » Read more

  • Part 3: Natural Language Processing – Sentiment Analysis and Opinion Mining

    November 24th, 2022 in AI, Azure, Big Data, Blog, Business Intelligence, Python, Synapse

    If you remember in part 2 we discussed what Key Word Analysis is and how this can be implemented to gain deeper insight from textual data. But we can go one step deeper and extract feelings and opinions from the same data. We can do this through Sentiment Analysis and Opinion mining! In this blog I will talk you through what they are and how we can implement them using Microsoft’s Cognitive Services. What is Sentiment Analysis? We should…

    » Read more

  • 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

  • How to Trigger a Power BI Dataset Refresh via Azure Synapse Analytics in 3 Simple Steps.

    June 27th, 2022 in Azure, Blog, Business Intelligence, Power BI, Synapse

    In this blog post we’ll explore how to trigger a Power BI Dataset via Azure Synapse Analytics in 3 simple steps: Step 1: Create a new security group called “AzureSynapsePowerBIIntegration” in your Azure Active Directory. Next add the name of your Azure Synapse Workspace as a member of your new security group. This will be your service principal. Step 2: Login to powerbi.com and in the top right hand corner locate “Settings” and…

    » 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

  • How to parameterise the Execute Pipeline activity in Azure Synapse Analytics.

    April 12th, 2022 in Azure, Blog, Data Factory, Synapse, Uncategorized

    Unfortunately, as of April 2022 there is not an option to parameterise or add dynamic content to an “Execute Pipeline” activity to invoke a pipeline run. However, with the use of a Microsoft API there is method which we can use to overcome this. In this blog post we’ll use the “Pipeline – Create Pipeline Run” API documented below: https://docs.microsoft.com/en-us/rest/api/synapse/data-plane/pipeline/create-pipeline-run…

    » 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 Synapse Series: What is Workload Management (part2)

    July 30th, 2021 in Azure, Blog, Business Intelligence, Synapse

    So in part 1 we found out what Workload Management was and why we wanted to use it. (If you haven’t read that part, please click here and read that post before you carry on, as we’re jumping in at the deep end!) In this part we’ll be learning how to configure it. Continuing from the end of the previous post, you have two main options to configure this. First is to assign a user or role to one of the existing system defined…

    » Read more

  • Azure Synapse Series: What is Workload Management (part1)

    June 10th, 2021 in Azure, Blog, Business Intelligence, Synapse

    So you’ve started using Synapse SQL Pools, you’ve scaled up to improve performance, but your queries aren’t going as fast as you’d hoped. You take a look at resource utilisation and you see that as you scale up, your total resource utilisation per query goes down. But didn’t we scale up to allow our query to use more resource and run quicker? What’s going on here? A little thing called Workload Management.…

    » Read more