SSIS Expression to Obtain Filename from Path

Cool post to find the Filename portion of a path that includes the filename.   Simply replace zFilename in two locations in the example below with the name of your user defined variable name.  This user defined variable must contain the full path including the filename.   Create another user defined variable to store the filename portion of the string, then assign it this expression:

REVERSE(SUBSTRING(REVERSE( @[User::zFilename] ), 1, FINDSTRING(REVERSE( @[User::zFilename] ),"\\", 1)-1))

Works a treat!

TSQL: Find Left and Right of String from a Delimiter

Example below shows how to split a string into left and right components based on a delimiter…

DECLARE @Str1 varchar(100), @Str2 varchar(100),@Delims nvarchar(max)
SET @Str1 = '$1000/hour'
SET @Delims =N' ,:/;-';

SELECT LEFT (@Str1,patindex(N'%['+@Delims+']%',@Str1+N',')-1) as LeftSide,
case when len(@Str1)-patindex(N'%['+@Delims+']%',@Str1+N',')>0 then
END AS RightSide

SSAS – Create Tabular Cube From PowerPivot database

Converting a PowerPivot model into an Analysis Services Tabular model can be seen as the wormhole bridge between the Business and I.T. worlds.  Self service B.I. gives the desktop Excel user the power to create sophisticated data models without much engagement with I.T. other than securing some data sources.   This gets the business going on refining concepts and ideas that avoids typical holdups and breakdowns – such as I.T. demanding detailed requirements specifications – and the business not knowing what they want until they see it.

You have your PowerPivot model ready and sitting on the file system – the offical guide to converting your PowerPivot model to Tabular is here …   You start with running SQL Server Data Tools, you select an Analysis Services Project, and you will see “Import From PowerPivot”.


Choose this and then browse for your PowerPivot xlsx document on your file system (direct import from the PowerPivot gallery in Sharepoint not yet supported).   Note that if your PowerPivot database contains some static content fed in from Excel worksheets (linked tables) this data is treated as a copy and paste and is stored in the Model.bim file.

Keeping going and you will see the PowerPivot model will get imported into your Model.Bim object looking very similar to the screen layout in your Excel PowerPivot development environment.  Right click on the project and select Properties and confirm deployment location for your Development Tabular instance.  Now right click the Project and select Deploy.

Now you have ported the model created by your Excel business user into a Corporate Analysis Services cube.   Tabular will add more scalability than PowerPivot model can provide and open other features like per user security to the data.  If the PowerPivot model will still remain the master source of changes, subsequent changes will need to be reimported back into Tabular using this same process.   If you havn’t been involved in the development process then it is likely you will want to confirm the design meets your Enterprise development standards before you put this into production.  Ideally you sit the Excel user down and educate on any obvious gaps in meeting standards to ensure future PowerPivot models are more closely aligned.

Unpivot Your World

Unpivoting your data means grabbing a data series from buckets across the columns of your data source (usually a spreadsheet) and transposing along the rows instead.   This can be accomplished with a wide variety of tools, however two will be covered here, T-SQL and the Data Explorer plugin for Excel 2013.

The TQL Example is provided below using a sample dataset that has the Year pivoted in 3 columns, 1 column for each year in the dataset, 2004, 2008 and 2012.  We UNPIVOT these years  into two values, a year and the value for that year.   The year we call [Years] and the value we call [Medals]  :

CREATE TABLE [dbo].[SomeData](
 [Country] [varchar](20) NULL,
 [Sport] [varchar](20) NULL,
 [2004] [int] NULL,
 [2008] [int] NULL,
 [2012] [int] NULL

INSERT [dbo].[SomeData] ([Country], [Sport], [2004], [2008], [2012]) VALUES (N'Australia', N'100m Womens', 14, 12, 3)
INSERT [dbo].[SomeData] ([Country], [Sport], [2004], [2008], [2012]) VALUES (N'Somalia', N'100m Womens', 0, 0, 0)
SELECT Country, Sport, Years, Medals
FROM [dbo].[SomeData] Main
[Medals] FOR Years IN (
) Sub

Another alternative is to utilise the latest release of the Data Explorer plug-in for Excel 2013.  This could make a lot of sense, give that your pivoted data source could be in Excel anyway.  You can find a link to the latest release of Data Explorer containing the Unpivot feature here

Happy Unpivoting.

Excel Power View – Changing your Table to a Chart

Excel Power View reports encourage you to drag the elements you need for your chart onto the report surface in the way of a table first, then you can convert that table to a number of different chart visualisations in a second step.  

Having trouble converting to a chart ?  First you need to have something worth aggregating in your table otherwise you won’t get an option to convert to a chart.  Secondly, make sure you click on any cell within the table, then a Design tab will appear in the ribbon somewhere between the Power View and PowerPivot tabs.

Click on the Design tab, then on the left choose from a number of different visualisations under the “Switch Visualization” section of the ribbon.  Note as you select each of the different charts, you may get a slightly different menu of Power View fields on the bottom right hand of the screen in which to configure and drive the chart. 

For Pie charts remember they are bad ( but if you still want to be bad, they can be found under “Other Charts”.  Remember however that Power View Pie Charts currently don’t support Data Labels ( so if this all sounds a bit shady, try a column chart.

Go forth and Visualise !