Different Types/Data Stores in Linked Services in Azure Data Factory

I am sure you know what a Linked Services in Azure Data Factory. If not, search my blog and read about it. I have already blogged about it earlier. Here, I’m going to list down all types/data stores that a Linked Service can connect to. This list will keep on changing in future as Azure team may remove/add other data stores.

Presently, there are 25 Types/Data Stores available for Linked Services to connect to. Here goes the list:

  1. Azure Storage:- [“type”: “AzureStorage”]
  2. Azure Storage SAS:- [“type”: “AzureStorageSas”]
  3. Azure Data Lake Store:- [“type”: “AzureDataLakeStore”]
  4. Azure SQL Database:- [“type”: “AzureSqlDatabase”]
  5. Azure SQL Data Warehouse:- [“type”: “AzureSqlDW”]
  6. Azure DocumentDB:- [“type”: “DocumentDb”]
  7. SQL Server (On-Premises):- [“type”: “OnPremisesSqlServer”]
  8. Cassandra:- [“type”: “OnPremisesCassandra”]
  9. MongoDB:- [“type”: “OnPremisesMongoDb”]
  10. Oracle:- [“type”: “OnPremisesOracle”]
  11. File System:- [“type”: “OnPremisesFileServer”]
  12. DB2:- [“type”: “OnPremisesDb2”]
  13. MySQL:- [“type”: “OnPremisesMySql”]
  14. Teradata:- [“type”: “OnPremisesTeradata”]
  15. PostgreSQL:- [“type”: “OnPremisesPostgreSql”]
  16. Sybase:- [“type”: “OnPremisesSybase”]
  17. HDFS:- [“type”: “Hdfs”]
  18. ODBC:- [“type”: “OnPremisesOdbc”]
  19. OData:- [“type”: “OData”]
  20. Web:- [“type”: “Web”]
  21. SalesForce:- [“type”: “Salesforce”]
  22. AmazonRedshift:- [“type”: “AmazonRedshift”]
  23. Amazon Web Services:- [“type”: “AwsAccessKey”]
  24. FtpServer:- [“type”: “FtpServer”]
  25. Azure Search:- [“type”: “AzureSearch”]

The “type” is mentioned in the JSON script and then we need to write “typeProperties” as per the type. For example, if type is AzureStorage, then we need to mention ConnectionString in the typeProperties section, if Web, then we need to mention Authentication Type and URL, etc.

I will be discussing each and every Data Store and its a few important properties in separate blogs. So, stay tuned!


What is Linked Services in Azure Data Factory

Linked Services in Azure Data Factory play the role of Connection String to external resources.

As we know that Data Factory is nothing but a kind of data integration cloud-service. In other words, it’s nothing but a cloud-based ETL service. Every ETL tool connects to data sources and destinations and processes the data. Hence, we require the connection details like Server Name or Drive Name/Folder, Blog Containers, Database Name, User Credentials, etc. We create a Linked Service in Data Factory to store this information, which can be used by the Activities in the pipelines. It helps use to connect to external resources like Azure SQL, on-prem SQL Server or Oracle, Azure HDInsight, Azure Storage, etc. We can create more than one Linked Services in the same Data Factory.

If you’re a SQL Server Integration Services (SSIS) developer, treat this as Connection String in Connection Manager. That’s it!

Developer usually add the word “LinkedServices” as suffix to the name of the Linked Services, though I prefer to add the word “LS”, which helps me out to have smaller names for these objects. Linked Services information is maintained as JSON in a physical file, which is also known as JSON Script.

Hope it helps you out to get an idea about the Linked Services.

Happy reading!

What is Azure Data Factory

In simple words, Azure Data Factory is nothing but an Extract, Transform and Load (ETL) tool, which does more than what an ETL tool can usually do and has been designed for the Cloud technology. You may say it’s a data integration service in the cloud. It’s just two and half years old kid of the Azure team, which is getting mature with the passage of time.

As per the MSDN documentation, Azure Data Factory is a cloud-based data integration service, to compose data storage, movement, and processing services into automated data pipelines.

From a BI developer perspective, it just a Cloud-based ETL tool. You can design connections (Linked Services), data store schema (DataSet/Table)  and an Activity, which help you to connect to a data source, pull the data out, transform it and then dump into a data destination. The beauty of this tool/service is that it can connect to diverse data stores i.e. relational to non-relational, big data to analytical data, which is very difficult to process using existing ETL tools such as SSIS or Informatica. And, the bonus is we can utilize Azure services i.e. Hardware/Software to improve the performance of the pipelines.

I have just given you an brief overview of Azure Data Factory. Click here if you wish to know more about it.

Happy reading!

Blogs on Cloud Technologies

Yes! I begin to write about the Cloud, the buzzword… 🙂  Will be writing on the Azure Data Services and the Data Factory…Stay tuned!

Data Sources Supported by Power BI

Wow! Just amazing!

Power BI can connect to anything and everything on this planet. Not much to write over here about the data sources supported by the Power BI. Just take a look at below images.

Power BI Desktop team provides upgrades in the first week of every month, earlier it was scheduled at the end of every month. Keep you Power BI Desktop upgraded all the time n get all new Data Sources released, if any. It’s just amazing to connect to so many different types data sources using this tool. Good job team!

fileds Read more…


Difference between SSRS and Power BI

Yes! There is difference between the SQL Server Reporting Services (SSRS) and the Power BI.

The bottom line is the SSRS is the developer-oriented enterprise-level reporting/visualization tool while the Power BI is client-oriented Self-Service BI tool. I’m sure this statement makes it pretty clear what I am going to say next.

SSRS is the developer-oriented tool means you need to know how to design a report, get the data from the data source and deploy it. If any change is required in the report, it should be done by the developer of the report and redeploy back on to Report Server. This creates a lot of problem when the client wants to view data from different perspectives. He has to come back to the development team and request for the same. The client has no control over report designing and digging out the data himself, at any point in time. And, that’s why lots of clients prefer to analyze financial and statistical data using Microsoft Excel tool, perfect for them.

Read more…

What is Power BI Embedded

Let me put it in simple words – Pay us and use Power BI visuals in your app, out-of-the-box. No Office365 subscription required. That’s it!

If you wish to use Power BI, you need to have Office365 subscription with you. Even if you wish to share the reports/dashboards with the client, you need to get him an Office365 subscription for him. It means pay up, and then you can bring your client in your network. That’s how he can use the Dashboards/Reports in Power BI. But, what if wish not to buy Office365 subscription for him? Can he still look into the Dashboards/Reports???

Yes, very much possible now. And, that’s where Power BI Embedded comes to your rescue.

Power BI Embedded is a part of Microsoft Azure infrastructure. It helps you to use REST APIs and the Power BI SDK to call the data visuals into your app. Yes! You need to pay for that as well but you can definitely share your visuals out-of-the-box i.e. Power BI Service. If you wish to use Power BI Embedded, the Microsoft Azure is the place to start off.

I tried this component and I think it’s pretty good. More to explore yet!