Data Platform Saturday Microsoft Canada May 28th 2016

Hi All,

As promised, I have posted the code and Power Point presentations for both presentations that I did in Mississauga on Saturday. Please feel free to download them. As always it would be super to hear from you should you have any questions and I do look forward to meeting you again in the near future. In the interim, happy programming!

Regards Steve

NowYouSeeIt

OData presentation

 

SQL Saturday 500 Boston: Code and PowerPoint presentation

Hi All,
As promised, I have posted the code from Saturday’s presentation along with the PowerPoint presentation.
You will need a copy of ContosoRetailDW.bak which is available from the Microsoft site. This zipped file was simply too big to include.
Do have fun with the code and as always should you have any questions or concerns, please feel free to contact me at steve.simon@sqlpass.org

regards Steve

 

DAX and the Tabular Model

Now exactly which proposal is this

Introduction

Oft times we are forced into situations where we clearly need to think outside of the box. A case at hand arose early in 2016 where one of our client’s required a “quick and dirty” mechanism to ascertain which proposal data were contained within CSV files that are generated as a part of a Dynamics CRM data transfer from the cloud to their local data warehouse.

Unorthodox Yes!!  Abnormal No!!

But I am getting ahead of myself. A bit of high level history:  Within the client’s firm, project proposals are generated within Dynamics CRM. Portions of this data including proposed costing must be captured and stored within the local data warehouses (for reporting purposes). This data is pushed to the data warehouse from Dynamics via a process that is set to “kick off” upon the completion of data entry within Dynamics CRM.

Every two minutes a SQL Server Agent job, polls to see if new data has been added to the warehouse. If there is new data, these new rows are extracted to a CSV file, otherwise the process goes to sleep. As the process of how this is achieved is out of the realm of this discussion, the avid reader is referred to an article that I wrote last year for SQL Shack.

http://www.sqlshack.com/creating-dynamically-generated-csv-files-containing-sql-server-data/

One point must be critically understood before we venture into defining a solution for the issue at hand. As pointed out in the article (listed above), extract files were created with the company name as the prefix and the date and time of the file creation was appended at run time (see the sample screen shot below).

FileWatcher01

Now, the contents of these CSV files are the vital data that the client requires. A sample proposal number may be seen on the second line of the screen shot below (its actual label is ”quote”). The contents of each of the CSV files is for one and only one proposal, thus the proposal number is the same for all records.

FileWatcher02

The plot now “thickens”: The client requested that the file names no longer be the name of the firm along with the date and time of creation (see above) but rather that the files be named with the actual proposal numbers. For those of us whom have worked on such issues, it is often challenging to perform operating system tasks such as dynamic file naming from within SQL Server Integration Services, once package execution and processing has commenced.

A drum roll please maestro!!! Let us see how this task may be achieved, however let us utilize .NET to achieve our goals, AFTER the extract file is created.

Getting started

The key to our solution is a nifty tool that the good people at Microsoft inserted into their earlier versions of Visual Studio. We are going to be utilizing a “FileSystemWatcher” to achieve our goals. In our case we shall be utilizing VB.Net, however we could utilize C# as well. The application will be running 24 x 7 and NO!!, it does not tie up much CPU as it lies dormant until a “file created” event is detected.

The important point for the reader to retain is that “FileSystemWatchers” have events and we shall utilize these events to our benefit.

The eagle-eyed reader will remember that the data is extracted from Dynamics and placed into the client’s warehouse. The extraction job runs every two minutes and will extract one and only one proposal at a time. We might say that it “Pops the stack”. The end result being that a disk based file is “created”.

We begin by creating a new Visual Basic .Net Windows application. We shall be utilizing Visual Studio 2015. Upon arrival at our first data entry screen, we give our VB.Net project a name (see below).

FileWatcher03

We click “OK” to continue and we arrive at our first form (see below).

FileWatcher04

The avid reader will note that we have a blank form visible. Believe it or not, we shall NOT be adding any visual controls to the screen itself short of the file system watcher as may be seen above. The “FileSystemWatcher” is found upon the “component” menu as may be seen below:

FileWatcher05

Configuring the “FileSystemWatcher”

FileWatcher06

We begin by configuring the FileSystemWatcher’s Property page (see above and to the bottom right. The “Watcher” is told  that it should be looking at the C:\Stellenbosch directory for new files HOWEVER it should only concern itself with files that satisfy the mask “Altico_*.csv.

This done..

By double clicking upon the “FileSystemWatcher” we open the code page for the watcher.
We begin by creating a PUBLIC subroutine which we shall call:  “Public Sub ProcessFile()”.
Having achieved this, we define a few variables that we shall be utilizing throughout the course of this exercise.
At the top level, we create a GLOBAL string variable called “Proposal” The purpose of this variable will become apparent within a few minutes.
I declare a few local variables within our subroutine.

Dim I, Z, Length, kount, before As Integer
Dim substr As String
I = 1

Being a guy whom was brought up as a FORTRAN programmer, the astute reader will note that I have used single letter to define counters. Let us put it that “Old habits die hard!”
We must understand that in order to ascertain the proposal number that we need read the file and that we really wish to process the whole file in one go. This said, we shall be utilizing a System.IO “Streamreader”. To do so, we shall include two libraries, System.IO and System.

Imports System
Imports System.IO

Within our subroutine ProcessFile(), we declare a StreamReader.

Dim fileReader As System.IO.StreamReader
fileReader =
My.Computer.FileSystem.OpenTextFileReader(“C:\Stellenbosch\Altico_.csv”)

Once a file is created within the directory shown above in the code, a “file created” event occurs; it fires if and only if the file name matches a predefined file name mask. Our subroutine “ProcessFile” is then executed if the filename and the masking, match. The code for this event may be seen below.

Private Sub FileSystemWatcher1_Created(sender As Object, e As FileSystemEventArgs)
Handles FileSystemWatcher1.Created
Call ProcessFile()
End Sub

Let the fun begin!!!

We declare a string variable called “stringReader”.
StringReader will contain the contents of the file. For each row within the file we must.

  • Eventually write the contents of the line to an output file.
  • Parse the second record of the file as it is the first data record that will contain the proposal number as the first line of the file is a header. There will always be a minimum of two lines within any data file (i.e. the header and at least one line of data).
  • Locate and capture the proposal number (which is in column 7).

The code to achieve this is shown below and it should be noted that one may stop the read process at the successful completion of parsing the second line of code. This exercise is left to the reader. The full code listing may be found in Addenda 1.

Dim stringReader As String
For Each row In “C:\Stellenbosch\Altico_.csv”
stringReader = fileReader.ReadLine()

If I = 2 Then

‘If you are on the second line of the file, begin a “Do while” loop to
‘parse the line to find the position of the sixth comma. The string
‘that we are looking for begins immediately after the 6th comma and continues until one character BEFORE the 7th comma (see below)

Do While kount < 6
Length = Len(stringReader)
Z = stringReader.IndexOf(“,”)
before = before + Z
stringReader = stringReader.Substring(Z + 1, Length – (before + 1))
substr = stringReader.Substring(0, stringReader.IndexOf(“,”))
‘It is important to note that within the iteration of the second record, each column of ‘the source data file,has its own turn to become the value of “Substring”. These values ‘are all false hits ‘UNTIL our Kount variable becomes 5. Our Global variable Proposal now ‘takes on this value. We shall look at the “Write to file “process in a few seconds.
If kount = 5 Then
Proposal = substr
‘ MsgBox(proposal)
End If
kount = kount + 1
Loop
End If
‘ MsgBox(“The first line of the file is ” & stringReader)
I = I + 1
Next

Writing our data contents to the output file.
Now that we have the “name” of the proposal, all that remains to do is to write the entire file contents to disc utilizing the value of “proposal” as a part of the TXT file name.
This is achieved utilizing the following code.
Before we work our way through the code however, it is important to remind the reader that the stream reader that we used above had reached the “end of file” marker. This said, we must re allocate it.
Further, we now allocate a “Streamwriter” (which originates from the System.IO library).

‘ Write to file
‘Declare our new file reader and open a Text file reader.
‘We shall now read a line and write the line to file. The astute reader will note the ‘usage of “Proposal” in the text WRITER on line 6 of the code in gold below.

Dim fileReader1 As System.IO.StreamReader
fileReader1 =
My.Computer.FileSystem.OpenTextFileReader(“C:\Stellenbosch\Altico_.csv”)
Dim stringReader1 As String
stringReader1 = fileReader1.ReadLine()
‘Declare our file writer. Note that in declaring the output file, we utilize the value
‘of our global variable “Proposal” to define the name of the output file.
Dim file As System.IO.StreamWriter
file = My.Computer.FileSystem.OpenTextFileWriter(“c:\Stellenbosch\” & Proposal & “.txt”, True)

‘In this section of code we perform the actual read and write process until the
‘end of input file marker is reached.
Do While (Not stringReader1 Is Nothing)
‘ MsgBox(stringReader1)
file.WriteLine(stringReader1)
stringReader1 = fileReader1.ReadLine()
Loop
‘Close the OUTPUT file
file.Close()

Let us give our project a test drive

FileWatcher07

We begin by selecting “Debug” and “Start Debugging” from the “Debug” Menu. Our form is brought to the screen.
At this point in time, the application is active and is quiescent.
Our C:\Stellenbosch directory may be seen below:

FileWatcher08

To prove that our project works, I am going to grab a copy of a file from another directory (see below).

FileWatcher09

Having copied the file, we now DROP the file into the “C:\Stellenbosch” directory.
This simulates the creation of a production file.

FileWatcher11

Once again, the “eagle-eyed” reader will note that the desired data file was created as highlighted in the screenshot above.
Conclusions

Whilst this is only one way of achieving our end goal, I prefer it as it separates the SQL Server from operating system interaction. There are most certainly other ways and means of achieving the same results.
The complete code listing may be found in the Addenda section immediately below:
As always, should you have any questions or concerns, please do feel free to contact me.
Happy programming to all of you!

Addenda 1
Imports System
Imports System.IO
Public Class Form1
Dim Proposal As String

Public Sub New()
‘ This call is required by the designer.
InitializeComponent()

End Sub
Public Sub ProcessFile()

Dim I, Z, Length, kount, before As Integer
Dim substr As String

I = 1

Dim fileReader As System.IO.StreamReader
fileReader =
My.Computer.FileSystem.OpenTextFileReader(“C:\Stellenbosch\Altico_.csv”)
Dim stringReader As String
‘Do While I <= 3
For Each row In “C:\Stellenbosch\Altico_.csv”
stringReader = fileReader.ReadLine()

‘MsgBox(“Hello”)
If I = 2 Then
Do While kount < 7
Length = Len(stringReader)
Z = stringReader.IndexOf(“,”)

before = before + Z
stringReader = stringReader.Substring(Z + 1, Length – (before + 1))
substr = stringReader.Substring(0, stringReader.IndexOf(“,”))
If kount = 5 Then
Proposal = substr
‘ MsgBox(proposal)
End If

kount = kount + 1
Loop
End If

‘ MsgBox(“The first line of the file is ” & stringReader)
I = I + 1
Next
kount = 1
” Write to file
Dim fileReader1 As System.IO.StreamReader
fileReader1 =
My.Computer.FileSystem.OpenTextFileReader(“C:\Stellenbosch\Altico_.csv”)
Dim stringReader1 As String
stringReader1 = fileReader1.ReadLine()
Dim file As System.IO.StreamWriter
file = My.Computer.FileSystem.OpenTextFileWriter(“c:\Stellenbosch\” & Proposal & “.txt”, True)
Do While (Not stringReader1 Is Nothing)
‘ MsgBox(stringReader1)
file.WriteLine(stringReader1)
stringReader1 = fileReader1.ReadLine()
Loop
file.Close()

End Sub

Private Sub FileSystemWatcher1_Created(sender As Object, e As FileSystemEventArgs) Handles FileSystemWatcher1.Created

Call ProcessFile()
End Sub

Private Sub FileSystemWatcher1_Changed(sender As Object, e As FileSystemEventArgs) Handles FileSystemWatcher1.Changed

End Sub

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

End Sub
End Class

SQL Saturday 360 Israel

Wow!! What an event. The event management team did a super job. Believe it or not,there were in excess of 160 people at the event. To those whom attended my presentation I would like to send a special thanks. It was super to meet you all. As promised I have placed the zip file containing the PowerPoint presentation and all the code that I utilized in the presentation immediately below:

As always, should you have any questions or concerns, please feel free to contact me.

SQLSaturday360IsraelFullZip