SSAS (SQL Server Analysis Services) – Getting all partition information from SSAS Database

I had a requirement where I wanted to find out and keep a constant record of exactly how my partitions were set up and created within SQL Server Analysis Services (SSAS). So below is a script that I found somewhere (If I find the source I will put it in hereJ) and how I inserted into a SQL Server Table so that I can use it for the creation and dropping of SSAS Partitions.

 

Example: We are going to be getting back all our SSAS Partition information from our AdventureWorksDW2012 cube.

 

You can get a copy of this here: http://msftdbprodsamples.codeplex.com/releases/view/55330

·         I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012

 

Installation of ASSP for Analysis Services

1.       If you have not got this installed already you would first need to install the ASSP for Analysis services.

2.       You can get the files from here:

https://asstoredprocedures.codeplex.com/releases/view/79180

3.       And then in order to install you can follow these easy to install instructions:

https://asstoredprocedures.codeplex.com/wikipage?title=Installation%20Instructions

 

Configuration andgetting down partitioned data

 

1.       The first thing that we did was to check to ensure that when running our MDX query it would return the required results from our cube.

2.       In SQL Server Management Studio (SSMS) we went into our Analysis Services, then went to our AdventureWorksDW2012 cube.

a.       Then right click, select New Query and then MDX

b.      clip_image001

c.       Once this opens run the following query:

call assp.DiscoverXmlMetadata(“Partition”)

d.      Now execute the query and you should see the following as shown below:

e.      clip_image002

f.        NOTE: There are a whole host more columns with a lot of valuable information.

                                                               i.      Below is often what I am most interested in, and the columns are on the right hand side

g.       clip_image003

3.       Then in order to store this information we are then going to use SSIS to Insert data from an MDX query into a SQL Server table.

a.       You can use this blog post below

b.      SSIS 2012 – INSERTING DATA INTO A SQL SERVER TABLE FROM AN MDX QUERY

4.       Then we created our table with the following syntax in order to get the data into our SQL Server Table as our destination.

CREATETABLE [Mart].[TD_SSAS_PartitionDetails](

       [Name] [varchar](300)NULL,

       [ID] [varchar](300)NULL,

       [CreatedTimeStamp] [varchar](300)NULL,

       [LastSchemaUpdate] [varchar](300)NULL,

       [Description] [varchar](300)NULL,

       [LastProcessed] [varchar](300)NULL,

       [State] [varchar](300)NULL,

       [Type] [varchar](300)NULL,

       [AggregationPrefix] [varchar](300)NULL,

       [StorageMode] [varchar](300)NULL,

       [CurrentStorageMode] [varchar](300)NULL,

       [StringStoresCompatibilityLevel] [varchar](300)NULL,

       [CurrentStringStoresCompatibilityLevel] [varchar](300)NULL,

       [ProcessingMode] [varchar](300)NULL,

       [ProcessingPriority] [varchar](300)NULL,

       [StorageLocation] [varchar](300)NULL,

       [RemoteDataSourceID] [varchar](300)NULL,

       [Slice] [varchar](300)NULL,

       [EstimatedRows] [varchar](300)NULL,

       [AggregationDesignID] [varchar](300)NULL,

       [EstimatedSize] [varchar](300)NULL,

       [Parent_MeasureGroupID] [varchar](300)NULL,

       [Parent_CubeID] [varchar](300)NULL,

       [Parent_DatabaseID] [varchar](300)NULL

)ON [PRIMARY]

 

GO

a.        Once completed our SSIS Data Flow task looked like the following:

b.       clip_image004

5.       Now once we run the above SSIS package we see our data in our SQL Server table from step 4 above.

6.       NOTE: If you want to keep a historical record you could then take this data and put it into a Fact style table.

a.        But for our purposes we just truncated the above table and inserted our data again daily.

 

Usage for SSAS Partition Details

·         The main reason for us getting our SSAS Partition details into a SQL Server table, is so that we could then get a list of our current partitions for our SSAS database and cube.

·         We could then use our Partition details to find out or Max and Min Partitions, and also how many partitions we have.

·         Another use is based on our Max Partition is to ensure that we can create partitions for our data that we are going to load going forward.

·         Likewise we could also find out our Min Partition to drop older partitions of data.

·         And finally because all our data is stored in a SQL Server Table, we could then use this data within SSIS using XMLA to dynamically create partitions.

o    You can use this blog post below as a reference:

SSIS – CREATING NEW PARTITIONS IN SQL SERVER ANALYSIS SERVICES (SSAS) WITH XMLA AND ANALYSIS SERVICES DDL

Excel–Power Query–Automating SSIS job duration report

Following on from our query to get our SSIS Job duration information, I decided to start using Power Query and see if I could use it to facilitate the automation of my reporting requirements.

Detailed below is how and what I archived.

Example

·         We are going to use our SSISDB to see if there have been any changes in how long it takes our entire job to complete.

·         NOTE: We always use a Parent package which runs all our child packages. So we are going to run the query using our Parent Package.

·         As with our Example we are going to view our Job which runs the SCOM Hourly collection

o   The parent package is called: SCOM Parent Package.dtsx

·         NOTE 2:The SSISDB is part of SQL Server 2012, so this query will only apply for you are using SQL Server 2012

Download and Install Power Query

You can download and install currently the latest version of Power Query from here:

http://www.microsoft.com/en-za/download/details.aspx?id=39379

Creating your Power Query to get information from SQL Server

Here we are first going to create our query and ensure that we can get down the required data.

1.       Create a new Excel document

2.       Ensure that you can see your Power Query in the ribbon

a.       clip_image002

3.       Now as per our example we are going to get our data from SQL Server so complete the following steps below.

a.       Click on From Database and then select From SQL Server Database

b.      clip_image003

c.       Now in the Microsoft SQL Database put in the connection to your Server and Database

                                                               i.      As seen below with our example:

                                                             ii.      clip_image004

                                                            iii.      NOTE: Change the Server to you SQL Server Instance where you have got the SSISDB installed and running.

d.      Then click on the arrow next to SQL statement

                                                               i.      NOTE: This is because we want to put in a specific SQL Query to get back out SSIS job information

e.  Then for our example and to view the duration of the packages in our SSIS Project we put in the following TSQL statement

      Select

        Distinct (EX.executable_id)

       ,executable_name

       ,(EXS.execution_duration/1000)asDurationInSeconds

       ,CONVERT(TIME,dateadd(ms,EXS.execution_duration,’01-01-1900′))asDurationInTime

       ,convert(Date,EXS.start_time)asExecutionDate

       ,SUBSTRING(convert(varchar(23),EXS.start_time),12,2)asStartHour

                           ,Ex.package_name

 

  FROM[internal].[executables]asEXwith (nolock)

       Innerjoininternal.executable_statisticsasEXSwith (nolock)

              onEX.executable_id=EXS.executable_id

  whereEx.package_name=‘SCOM Parent Package.dtsx’

f.        Then click Ok.

g.       You will then get prompted if you want to Encrypt the connection

                                                               i.      Due to us running this within our domain we removed the tick

                                                             ii.      clip_image005

h.      Once run you should then see the following as shown below:

i.         clip_image007

j.        What I also did was to change the Name of the query.

                                                               i.      If you look on the right hand side under the Query Settings, where it says Name, as with our example we changed it to the following:

1.       clip_image008

k.       Then click on Apply and Close

                                                               i.      clip_image009

4.       You will now see the following table created:

a.       clip_image011

b.      NOTE: You will see that your table name has the same name as your Power Query name.

5.       Now save your Excel Workbook so that you have all your settings changed.

Creating the Invoke function in order to pass details to your query

In this section is where we will convert our query to use the Invoke Function so that we can then pass details to our query

1.       Right click on your Workbook Queries and select Edit

a.       clip_image012

2.       Then in the Query Editor Ribbon click on View and then click on Advanced Editor

a.       clip_image013

3.       This will then open the Advanced Editor.

4.       Now what we are going to do is to add some text which will allow us to create the Invoke Function.

a.       NOTE: I am very new to b so there might be a much more elegant or better way of doing this. So if you have any suggestions please let me know.

b.      Originally it looks like the following:

                                                               i.      clip_image014

                                                             ii.      NOTE: The query does carry on, on the right hand side.

c.       We then put in the following:

                                                               i.      At the start we put in the following which will create our Invoke Function

let SSISJobDetails = (SSISJobDetails as text)=>

1.       NOTE: We have defined a name for when we call our Invoke Function as said that we are accepting Text

                                                             ii.      We then put this at the bottom to close off our Invoke Function

in

   SSISJobDetails

                                                            iii.      It now looks like the following:

                                                           iv.      clip_image015

d.      Then click Done.

e.      NOTE: We are not quite finished yet, but we want to ensure that the Invoke Function will work first. Baby steps here J

5.       Once done it should then come back and look like the following as shown below.

a.       clip_image016

b.      NOTE: This is what we want to see.

6.       Now let’s test to ensure that everything is correct.

a.       Click on Invoke and you should see the following:

b.      clip_image017

c.       Put in anything into the text box, and then click Ok.

                                                               i.      NOTE: Currently we have not put in our expected variable into our Invoke Function which we will do in the next steps.

d.      Now you should see the output of the query:

e.      On the right hand side under Query settings in the Applied Steps you will see the following:

                                                               i.      clip_image018

f.        Click on the delete button next to InvokedSSISJobDetails

                                                               i.      NOTE: The reason that we are deleting this is because we do not want this dataset as it currently is.

g.       Once done it will go back to the same screen as in step 5 above.

7.       Now we are going to change our actual query so that what we pass from the Invoke Function will then be passed to the query

a.       Then in the Query Editor Ribbon click on View and then click on Advanced Editor

                                                               i.      clip_image013[1]

b.      Now because we want to change our Package_Name, this is right at the end of our query so we need to scroll right to the end.

c.       Now change it from what it currently is:

where Ex.package_name = ‘SCOM Parent Package.dtsx'”])

d.      To the following:

where Ex.package_name = ‘”&SSISJobDetails&”‘”])

e.      NOTE: We are using the nabme under our Applied Steps.

f.        Then click Done.

8.       Now to test click on the Invoke button

a.       Now what we will first test is out our existing SSIS Package name so we put in the following:

                                                               i.      clip_image019

b.      Then click Ok.

c.       You will then get the following as shown below:

                                                               i.      clip_image020

                                                             ii.      Click on Edit Permission

d.      This will then bring up the Native Database Query

                                                               i.      clip_image021

                                                             ii.      Click on Run

e.      Once completed you will now see your output as we had in step 3h above

f.        clip_image022

9.       Once again on the right hand side under Query settings in the Applied Steps you will see the following:

                                                               i.      clip_image018[1]

b.      Click on the delete button next to InvokedSSISJobDetails

                                                               i.      NOTE: The reason that we are deleting this is because we do not want this dataset as it currently is.

10.   Now click on the Invoke button but this time put in either another SSIS package that you have, or in my example I put in the letterA

a.       NOTE: I did this to ensure that it indeed passing the data from the Invoke Function into our query

b.      clip_image023

c.       I then clicked Ok and got the following:

                                                               i.      NOTE: You might get the Permissions required to run, if you do just b

d.      clip_image025

e.      This is what we expected to see, because it did indeed pass the value through to our query

11.   Now close down the Query Editor and it will prompt you if you want to keep your changes.

a.       Click on Keep

b.      clip_image026

12.   Once again on the right hand side under Query settings in the Applied Steps you will see the following:

                                                               i.      clip_image018[2]

b.      Click on the delete button next to InvokedSSISJobDetails

                                                               i.      NOTE: The reason that we are deleting this is because we do not want this dataset as it currently is.

13.   Now when you are back in your Excel Workbook you will see that your sheet looks like the following:

a.       clip_image027

b.      This is to be expected due to creating our Invoke Function. And due to their being no data from this, we have no data to display.

14.   When back in your Excel Workbook click on Save to save all your changes

Creating your Parameter to be passed to your Power Query

In the next steps we are going to be passing our parameter into our Power Query so that the user can simply put in the package name that they want to view.

1.       Create a new sheet in your workbook.

2.       With our example and in order just to know which sheets represented our Parameter we renamed our sheet to: Input Parameter

a.       clip_image028

3.       Next we created the following in our sheet and put it into a table

a.       clip_image029

b.      So once completed it looked like the following:

                                                               i.      clip_image030

c.       NOTE: The reason that we did not leave any spaces in our Column name is so that there is less formatting in our Power Query. I read this a few times when learning how to complete all the steps.

4.       Another important thing to note is that your parameter that is part of your query MUST have the same column name within your query

a.       If you do not have it then when trying to add the data to your table in the next steps it will keep on failing

b.      So with our example our Parameter had our PackageName, in our Input Parameter Sheet, as well as the data returned from the query

5.       Next click on Power Query and then click on From Table

a.       clip_image031

b.      This will then open your Query Editor

c.       And you will see the following:

d.      clip_image032

6.       What we did was to rename our query as per our example to the following:

a.       clip_image033

7.       Now in your query right click in SSISPackageName and select Insert Custom Column

a.       clip_image034

b.      Where it says New Column Name you can change it from Custom to something more descriptive.

                                                               i.      As with our Example we changed it to the following:

                                                             ii.      clip_image035

c.       Now where it says Custom column formula we put in the following:

SSISJobDetails([SSISPackageName])

                                                               i.      clip_image036

d.      Click Ok.

e.      You will then get prompted about data privacy

                                                               i.      NOTE: This is to ensure that your data is kept private

                                                             ii.      clip_image037

f.        ClickContinue

g.       This will then open the Privacy Levels window

                                                               i.      You can then select your Privacy Level

1.       clip_image038

                                                             ii.      We selected Organizational for all our connections

h.      Then click Save.

8.       Now you will see the following in your Power Query Window

a.       clip_image039

9.       Click on the arrows button in your SSISQueryDetails column and select Expand as shown below

a.       clip_image040

b.      Then click Ok.

10.   You should now see all that data from your query as shown below:

a.       clip_image042

b.      NOTE: You can then change and modify anything as you would with any normal Power Query dataset.

c.       We configured our DurationInSeconds column to Number

d.      And then our ExecutionDate to Date/Time

11.   Then click Apply and Close.

12.   This will then create a new sheet with your data.

a.       NOTE: For our Load settings we also selected to Load Data to Model

                                                               i.      This is so that we can store the data as efficiently as possible. As well as that data being stored in Power Pivot.

b.      clip_image043

13.   Now you have your data stored in your Excel Workbook.

Creating our Report and Chart

Next we will be creating our report and chart to show the actual information.

1.       First on your sheet where you have your output from your Power Query, we renamed this to something more descriptive.

a.       EG: Output PQ

2.       Next click on any column in your Power Query dataset in your Excel workbook.

a.       clip_image044

3.       Then in the ribbon on the far right hand side under TABLE TOOLS click on DESIGN

a.       clip_image045

4.       Now under Tools click on Summarize with Pivot Table

a.       clip_image046

b.      This will then open the Create Pivot Table and you will see as with our example below that it will have selected our Table

c.       clip_image047

d.      Then click Ok.

5.       Once again we first renamed our sheet to be called: Report PQ

6.       Then what we did was to create our report with the following shown below from our query.

a.       clip_image048

7.       We then created a chart so that it would be easier to display and view the data

8.       As with our data we got the following chart

a.       clip_image049

9.       Now the consumer of the report can go and see why the time has been increasing.

10.   What I then did was to hide some of the sheets so that it is easier for the end user to use. This is shown below.

a.       clip_image050

11.   The final test will be to change your SSIS Package Name on the Input Parameter sheet.

a.       Then click Refresh All under the DATA Ribbon

                                                               i.      clip_image051

b.      And then see if your report changes accordingly.

SSIS – Package duration time

Just a quick blog post today.

I wanted to see how long all my packages were taking to run in SSIS on SQL Server 2012, due to there being some storage issues, which related to the jobs taking longer to run.

So below is the query that I used to pass my parent package, so that I could then get back all the child packages.

Select

        Distinct (EX.executable_id)

       ,executable_name

       ,(EXS.execution_duration/1000)asDurationInSeconds

       ,CONVERT(TIME,dateadd(ms,EXS.execution_duration,’01-01-1900′))asDurationInTime

       ,convert(Date,EXS.start_time)asExecutionDate

       ,SUBSTRING(convert(varchar(23),EXS.start_time),12,2)asStartHour

       ,Ex.package_name

 

  FROM[internal].[executables]asEXwith (nolock)

       Innerjoininternal.executable_statisticsasEXSwith (nolock)

              onEX.executable_id=EXS.executable_id

  whereEx.package_name=‘SCOM Parent Package.dtsx’

 

NOTE: Just change the Ex.Package_Name to your package you want to view the SSIS history for.

In my next blog Post I will show you how to use Power Query and by passing a parameter to then view the output of your query.

SSRS – Viewing status of Data Driven Subscriptions

What I wanted to do, is to see the status of my data driven subscriptions which are emailed out.

I wanted to ensure that there were no errors when being sent out, as well as ensuring that they were indeed sent out.

NOTE: This follows on from the FRAMEWORK FOR AUTOMATING SQL SERVER REPORTING SERVICES (SSRS) DATA DRIVEN SUBSCRIPTIONS – PART 1 and FRAMEWORK FOR AUTOMATING SQL SERVER REPORTING SERVICES (SSRS) DATA DRIVEN SUBSCRIPTIONS – PART 2

1.       Log into your SQL Server and open SQL Server Management Studio, to where you have your Reporting Services installed.

2.       Then you can run the following query on your ReportServer$InstanceName

Select

                                 [Description]asDataDrivenSubscriptionName

                                ,LastStatus

                                ,ModifiedDateasLastRunDate

from[ReportServer$InstanceName].[dbo].[Subscriptions]with (nolock)

where[Description]=‘Internet Sales – Report Subscription from SharePoint’

a.       NOTE: For the above we used our Data Driven Subscription Name: Internet Sales – Report Subscription from SharePoint

3.       And as you can see below this was our output:

a.       clip_image001[6]

4.       In a future I will explain how to take this output and put it into an email via SSIS so that this can be emailed to you daily.