SSIS–Conditional Split based on Date

Below is a quick blog where I am using a conditional split based on your Date Column, so that you can then output your data to different tables or destination data flow tasks.

 

I also found that there are bits and pieces of information as to how to use the Expressions within the Conditional Split. So this might help someone looking on how to achieve this.

 

Example Scenario

 

·         In my example I am going to conditionally split the data by Month, so that when the conditional split has completed it will then direct the output to either the current month or the previous month.

·         The column which contains the DateTime data type is called:

o    DateTimeCreated

 

1.       Go into your Data Flow Task and either create your query or stored Proc for your Data Flow Source

2.       Then from your SSIS Toolbox drag in the Conditional Split

a.        clip_image001[4]

3.       Now double click on the Conditional Split and you will need to configure it with the following for the Current Month

a.        Where it says Output Name, put in CurrentMonth

b.       Then where it says Condition put in the following below, which will be explained afterwards how this works:

DATEPART(“MONTH”,ImportDateTime)==DATEPART(“MONTH”,GETDATE())

                                                               i.      What we are doing above is using the DatePart and getting the Month For our DateTime column which is called DateTimeCreated, highlighted in RED

                                                              ii.      Next we are using the compare or equal to for our SSIS Condition, highlighted in PURPLE

                                                            iii.      The final part is what you are comparing it to, so here we are comparing it to the current date, highlighted in ORANGE

c.        Now to do this for the Previous Month there is only one addition to your Condition, which is shown below

                                                               i.      Put in the Output Name of PreviousMonth

                                                              ii.      DATEPART(“MONTH”,DateTimeCreated) == DATEPART(“MONTH”,GETDATE())– 1

                                                            iii.      Here we have put a -1 at the end of our comparison, because this is outside of the DatePart function which is for our Month it is subtracting one month from our Current Date, highlighted in GREEN

d.       This is what it will look like once completed:

e.       clip_image003[4]

4.       Then the final step is to then drag your Data Flow Destination flow item to your Data Flow Destination

a.        Once dropped onto your Data flow Destination you will then get the Input Output Selection Window.

b.       Click on the Drop down and depending on your Data Flow Destination select your Output

                                                               i.      In the screenshot below it was the CurrentMonth

                                                              ii.      clip_image005[4]

5.       So once completed it will look like the following:

6.       clip_image006[4]

 

 

SSRS–Creating reports using Custom MDX or DAX to query SQL Server Analysis Services (SSAS) Cube both OLAP and Tabular (Not using the Wizard)

By default in SSRS you can query an SSAS Cube, but this is by using the drag and drop functionality. The issue with this is that it has quite a few limitations. One of the biggest limitations that I found is that you cannot use your own Custom MDX to create your data set.

NOTE: You can use the same details below to connect to the Tabular Version of SSAS, and then use DAX

Following on later from this blog, I will also explain and show you how to pass parameters to your MDX Query so that you can dynamically change your MDX query based on the users parameter selection.

Files required

You can get the SSAS and data warehouse files from the following location below, which is from Microsoft:

·         This is the Data warehouse files which are used in your Multidimensional Project in order to get the data for your SSAS Cube:

http://download-codeplex.sec.s-msft.com/Download/Release?ProjectName=msftdbprodsamples&DownloadId=417885&FileTime=129852962431370000&Build=20602

·         This is the Multidimensional Project which you can use to deploy to your SSAS Instance:

http://download-codeplex.sec.s-msft.com/Download/Release?ProjectName=msftdbprodsamples&DownloadId=258486&FileTime=129760394851070000&Build=20602

 

Expected outcome

What we are going to achieve is to create an SSRS Report, which will get its data from an SSAS Cube, using our Custom MDX Query. And we will show the Internet Sales, by Date and Country

 

Creating a Shared Data set to your SSAS Instance

1.       First thing you will need to do is have created your SSRS Project.

2.       Next is to right click on Shared Data Sources and select the following:

a.        clip_image001

3.       This will then open the Shared Data Source Properties Window

a.        Where it says Name, change this from the default to a meaningful name which will describe the type of data source as well as where you are connecting go.

b.       In our example I will give it the name of SSAS_AdventureWorks

c.        Where it says Type, click on the drop down and select OLE DB

                                                               i.      NOTE: The reason why we are doing this, is because the OLE DB provider is the only provider which we can then use to Query the SSAS Cube.

d.       Then click on Edit

                                                               i.      Where it says OLE DB Provider click on the drop down and select the following:

1.       clip_image002

2.       NOTE: The reason I have Microsoft OLE DB Provider for Analysis Services 11.0 is because I am running SQL Server 2012, you might have version 10.0 if you are running SQL Server 2008 R2

                                                              ii.      Where it says Enter a Server or File name, put in the connection to your SSAS instance.

1.       In our example our server was called ServerName

                                                            iii.      Then were it has the Log onto the Server, select Use Windows NT Integrated Security

1.       NOTE: This is the only option that you can select, because it is currently the only means of authentication to SSAS

                                                            iv.      Then were it says Initial Catalog, click on the drop down and select your SSAS Database.

1.       In our example we selected AventureWorksDW2012

2.       NOTE: By clicking on the drop down, you will also have to authenticate which is a good test to ensure that you can connect to your SSAS instance.

                                                              v.      ClickOk

                                                            vi.      This is what it will look like below once completed.

                                                           vii.      clip_image003

e.       Click Ok again.

f.         And this is what it will look like when you have finished creating your Shared Data Source

g.        clip_image004

4.       Now you will see the following under your Shared Data Sources

a.        clip_image005

5.       Now add a report to your Project.

 

Creating, configuring and setting up dataset using your MDX Custom Query

1.       Next we are going to create our data source and our data set.

2.       Right click on Data Source and select Add Data Source.

a.        We then configured our Data Source as shown below:

                                                               i.      clip_image006

b.       NOTE: We always give our Data Source Name the same name as our Shared Data Source Reference.

                                                               i.      We do this so when we need to troubleshoot we know which Shared Data source to look at.

c.        Click Ok

3.       Next right click on Datasets and select Add Dataset, which will open the Dataset Properties Window

a.        Under Name, once again give it a meaningful name, so that later when you need to troubleshoot you know which query to look at and potentially what data is has.

b.       For our Example we have it the following name:

                                                               i.      InternetSales_Date_Country

c.        Then select Use a dataset embedded in my report

d.       Click on the drop down next to Data Source and select the one we created above:

                                                               i.      SSAS_AdventureWorks

e.       Now where it says Query Type, leave the default which is Text.

f.         NOTE: You should have your MDX Query ready and tested.

                                                               i.      I had done this already by writing and testing my MDX query in SQL Server Management Studio (SSMS)

g.        Now in the Query Text box paste in your query

                                                               i.      As per our example we pasted the following:

Selectnonempty

       {

              [Measures].[Internet Sales Amount]

       } on 0

      ,nonempty

       {(

              [Ship Date].[Date].Children

              ,[Sales Territory].[Sales Territory Country].Children

       )} on 1

from [Adventure Works]

h.       Next click on the Fields in the left hand Window

                                                               i.      clip_image007

                                                              ii.      NOTE: The reason that we do this is because by doing this, it has to run the query to get the Field Names and Field Source from your SSAS Instance.

                                                            iii.      I then change the Field Name to be shorter and more meaningful as shown below:

                                                            iv.      clip_image008

i.         If you click back onto Query you will see the following configured:

                                                               i.      clip_image009

4.       You will now see your dataset created in SSRS

a.        clip_image010

5.       Now the final part is to create your SSRS Report, as you normally would using your Datasets.

6.       I created a quick Sample report based on the above data below:

7.       clip_image012