Retrieve JSON Data from SQL Server using a Stored Procedure and C#

By:
On:
In: Data Integration
With: 4 Comments

The previous post demonstrated how to load JSON into SQL Server using a stored procedure and C#. This post will examine the opposite – how to retrieve JSON data from SQL Server using a stored procedure and C#. Just like with the JSON loading technique, the new JSON functions in SQL Server 2016 allow for this retrieval technique. Here’s how it works in short: Data stored in a database table will be converted to JSON text in the stored procedure and returned to the C# client via an output parameter. SQL Server Table Let’s start with the SQL Server table, which will contain one rowRead More →

Load JSON Into SQL Server Using a Stored Procedure and C#

By:
On:
In: Data Integration
With: 1 Comment

This post will demonstrate how to load JSON into SQL Server using a stored procedure and C#. This technique is possible with the new JSON functions starting in SQL Server 2016, and is nice for the following reasons: Simplifies application code. Just serialize the C# objects to JSON and send them to the database. Executing a stored procedure with C# is easily done with a small amount of code. JSON is widely used and familiar to developers. Structural changes to the data could potentially be done entirely on the database side, meaning that you could make changes without having to touch the application code. SQLRead More →

Push Performance Counter Data into a Power BI Streaming Dataset

By:
On:
In: Power BI, SSAS Tabular
With: 0 Comments

In the previous post I showed how you can Push Data into Power BI Streaming Datasets with C#. That example used dummy data. In this post I’ll show how to push performance counter data into a Power BI Streaming Dataset as a real world example. This scenario allows for monitoring a computer or application in near real time in the browser. I won’t go through the steps of creating a Power BI Streaming Dataset. You can reference my previous post if you need instructions. I will note that the value names that you choose in the Streaming Dataset must match the C# property names for theRead More →

Power BI Streaming Datasets with C#

By:
On:
In: Power BI
With: 3 Comments

This post will demonstrate how to push data into Power BI Streaming Datasets with C#. For demo purposes I normally use LINQPad to run the code, but you could also create a .Net or .Net Core console application. LINQPad is an excellent, lightweight scratchpad for C# and other .Net languages. Power BI Streaming Datasets are a very cool feature because dashboard tiles that use them update in real time. You don’t have to refresh the browser window to display new data. With this feature you can watch your data in near real-time. This could be compelling in scenarios involving sensors, IoT, website traffic, etc. Here isRead More →

By:
On:
In: SSAS Tabular
With: 1 Comment

The previous post looked at how to process SSAS Tabular models with TMSL. Since SQL Server adds new JSON capabilities in 2016, let’s look at how to convert TMSL JSON to a Table with OPENJSON. OPENJSON is a new function in SQL Server 2016 that, per Microsoft: OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns. In short, OPENJSON converts JSON text to a table. Since TMSL is JSON, this function can convert a SSAS Tabular processing script into a table. This could be useful if you wanted to document a SSASRead More →

Process SSAS Tabular Tables and Partitions with TMSL

By:
On:
In: SSAS Tabular
With: 18 Comments

This post shows how to process SSAS Tabular tables and partitions with TMSL. TMSL stands for Tabular Model Scripting Language, and it is used for working with SSAS Tabular databases programmatically starting with compatibility level 1200 (SQL Server 2016). Multidimensional models and previous versions of SQL Server all use Analysis Services Scripting Language (ASSL for XMLA). A common use for TMSL is automating the processing of tabular models using the SQL Agent, SSIS, PowerShell, or some other application. One typical processing sequence is a series of Process Data commands followed by a Process Recalc at the end. Here’s what the TMSL code looks like in this scenario:

Read More →

Download JSON Data with SSIS

By:
On:
In: Data Integration
With: 12 Comments

In a post on the Allegient blog, I showed how you can download JSON data with PowerShell (Link no longer available due to company purchase and reorganization). Now we’ll take a look at how to download JSON data with SSIS. As a dedicated data integration tool, SSIS has many advantages over PowerShell for this type of work. SSIS does not have a built in source component for JSON data, so you’ll have to use the Script Component. When you drag the Script Component into a data flow, you’ll be asked how you want to use it. In this case you’ll use it as a source. DefineRead More →

Call an Azure Function from Power BI

By:
On:
In: Data Integration, Power BI, Power Query
With: 2 Comments

This post shows how to call an Azure Function from Power BI. This scenario offers some interesting possibilities, because it allows for integrating the power of several common programming languages with Power BI and Power Query. If you’re not familiar with Azure Functions, it’s a service that allows you to run small pieces of code without having to deal with a server. In this example, we’ll write a query in Power BI that submits a time zone to an Azure Function. The function simply returns the current time in the specified time zone. While this is a very basic example, it is important because itRead More →

Read the Top N Rows from Large Text Files with C#

By:
On:
In: Data Integration
With: 0 Comments

This post describes one way that you can read the top N rows from large text files with C#. This is very useful when working with giant files that are too big to open, but you need to view a portion of them to determine the schema, data types, etc. I’ve used PowerShell many times to do this with large csv files, but in this example we’re going to use C# and look at the Wikipedia XML dump of pages and articles. The 3017-03-01 dump is very large and comes in at 59.5 GB. The script is short and simple. All it does is readRead More →

Delete SSAS Partitions with TMSL and PowerShell

By:
On:
In: PowerShell, SSAS Tabular
With: 2 Comments

This post demonstrates how to delete SSAS partitions with TMSL and PowerShell. TMSL stands for Tabular Model Scripting Language, and it is used for working with SSAS Tabular databases programmatically at compatibility level 1200 (SQL Server 2016). Multidimensional models and previous versions of SQL Server all use Analysis Services Scripting Language (ASSL for XMLA). A common use for TMSL is automating the processing of tabular models using the SQL Agent, SSIS, PowerShell, or some other application. Another use could be deleting partitions used for development after deploying a model. That process goes like this: A partition is created for development purposes that contains a subset of theRead More →