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 […]

TSQL: Update the name of a column

Rename a column in an existing table with sp_rename.   Example below, just substitute values for schema, table, existingcolumnname and newcolumnname.   COLUMN keyword is fixed and does not need to be subsituted.

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 […]

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 […]

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 […]

PowerPivot DAX – Percentage of Total

Need a percentage of total  measure in DAX?   Read on… Lets say you want a report on products, showing % of each product’s Sale Amount to the total Sale Amount for all products selected. Business Formula is SUM(Product Sales) / SUM (All Selected Product Sales). Best to split the formula up into a set of seperate measures, […]