POWER BI For Excel & Office 365

It’s easier than ever to transform complex data into meaningful insights and share them with your team. Power BI is a powerful new way to visualize and interact with data in Excel and Office 365. Search, discover, and with just a few clicks, analyse, shape, and transform your data into stunning interactive visualizations that uncover hidden insights. Work, collaborate, and share from anywhere, on any device with Power BI for Office 365.

POWER BI For Excel & Office 365

Image | Posted on by | Leave a comment

Populate your dimensions with Unknown Members

Normally in every dimension load my first task is to insert the unknown value in the dimension table. This is tedious and time consuming task if dimension has many columns with different data types. And to make the script and put that script back into SSIS execute SQL task.

As usual then I will start googling and find the very Nice article Populate your dimensions with Unknown Members of Karl Beran. Which is dynamic in nature but I want to make it to dynamic from SSIS point of view. So simply I added the code which looking for the unknown value in dimension. If unknown is present then It by pass the code which populate the value into the dimension.

Note: @TableName is Parametrized variable.

And it’s enabling the identity insert before executing the insert statement and disabling the identity insert statement after the execute statement.

Simply put following code into the SSIS execute SQL task and do parameter mapping which contains the table name to pass this script. ALL Done for automated Unknown statement in SSIS.

—-Script which I love

—– Check if Unknown already Exists…..

DECLARE @sqlCommand nvarchar(1000)

DECLARE @counts int

Declare @TableName AS Varchar(500)

SET @TableName =   ?

SET @sqlCommand = ‘select @cnt=count(*) FROM ‘ + @TableName +  ‘ where id =-1′

EXECUTE sp_executesql @sqlCommand, N’@TableName varchar(500),@cnt int OUTPUT’, @TableName = @TableName, @cnt=@counts OUTPUT

Print @counts

—-Check if unknown is not available then insert unknown

IF @counts = 0

Begin

DECLARE @crlf CHAR(2)

SET @crlf = char(13) + char(10)

DECLARE @UnknownSurrogateKeyValue AS VARCHAR(255)

DECLARE @SurrogateKeyName AS VARCHAR(255)

DECLARE @DimensionTableNamePrefix AS VARCHAR(255)

DECLARE @SQL AS VARCHAR(5000)

–Unknown member surrogate key value:

SET @UnknownSurrogateKeyValue = ‘-1’

–String within dimension surrogate key column name

SET @SurrogateKeyName = ‘key’

–Prefix for Dimension table names

SET @DimensionTableNamePrefix = ‘Dim’

–Create Table for default values

DECLARE @dataTypes AS TABLE

(

DATA_TYPE     VARCHAR(50)

,MIN_DESC     VARCHAR(255)

,MAX_DESC     VARCHAR(255)

,DEFAULT_DESC VARCHAR(255)

)

INSERT INTO @dataTypes

(DATA_TYPE

,MIN_DESC

,MAX_DESC

,DEFAULT_DESC)

–Exact Numerics

SELECT  ‘bigint’

,’-9223372036854775808′

,’9223372036854775807′

,’-1′ UNION ALL

SELECT      ‘numeric’

,’0′

,’0′

,’0′  UNION ALL

SELECT      ‘bit’

,’0′

,’1′

,’1’UNION ALL

SELECT      ‘smallint’

,’-32768′

,’32767′

,’0’UNION ALL

SELECT      ‘decimal’

,’0′

,’0′

,’0’UNION ALL

SELECT      ‘smallmoney’

,’-214748.3648′

,’214748.3647′

,’0’UNION ALL

SELECT      ‘int’

,’-2147483648′

,’2147483647′

,’0’UNION ALL

SELECT      ‘tinyint’

,’0′

,’255′

,’0’UNION ALL

SELECT      ‘money ‘

,’-922337203685477.5808′

,’922337203685477.5807′

,’0’UNION ALL

–Approximate Numerics

SELECT      ‘float’

,’0′

,’0′

,’0’UNION ALL

SELECT      ‘real’

,’0′

,’0′

,’0’UNION ALL

–Binary Strings

SELECT      ‘binary’

,’0′

,’1′

,’0’UNION ALL

SELECT      ‘varbinary’

,’0′

,’1′

,’0’UNION ALL

–Other Data Types

SELECT      ‘sql_variant’

,’0′

,’1′

,’0’UNION ALL

–Date and Time

SELECT      ‘date’

,’0001-01-01′

,’9999-12-31′

,’1900-01-01’UNION ALL

SELECT      ‘datetimeoffset’

,’0001-01-01 00:00:00.0000000 +00:00′

,’9999-12-31 23:59:59.9999999 +00:00′

,’1900-01-01 00:00:00.0000000 +00:00’UNION ALL

SELECT      ‘datetime2′

,’0001-01-01 00:00:00.0000000′

,’9999-12-31 23:59:59.9999999’

,’1900-01-01 00:00:00.0000000’UNION ALL

SELECT      ‘smalldatetime’

,’1900-01-01 00:00:00′

,’2079-06-06 23:59:59′

,’1900-01-01 00:00:00’UNION ALL

SELECT      ‘datetime’

,’1753-01-01 00:00:00.000′

,’9999-12-31 23:59:59.997′

,’1900-01-01 00:00:00.000’UNION ALL

SELECT      ‘time’

,’00:00:00.0000000′

,’23:59:59.9999999′

,’00:00:00’UNION ALL

–Character Strings

SELECT      ‘char’

,’U’

,’Unknown’

,’UNK’ UNION ALL

SELECT      ‘varchar’

,’U’

,’Unknown’

,’UNK’ UNION ALL

SELECT      ‘text’

,’U’

,’Unknown’

,’UNK’ UNION ALL

–Unicode Character Strings

SELECT      ‘nchar’

,’U’

,’Unknown’

,’UNK’ UNION ALL

SELECT      ‘nvarchar’

,’U’

,’Unknown’

,’UNK’ UNION ALL

SELECT      ‘ntext’

,’U’

,’Unknown’

,’UNK’ UNION ALL

–Binary Strings

SELECT      ‘image’

,”

,”

,” UNION ALL

–Other Data Types

SELECT      ‘hierarchyid’

,’/’

,’/’

,’/’ UNION ALL

SELECT      ‘uniqueidentifier’

,’null’

,’null’

,’null’ UNION ALL

SELECT      ‘xml’

,”

,”

,”

— IDENTITY INSERT ON

SELECT @SQL = ‘SET  identity_INSERT ‘ + t.TABLE_SCHEMA +’.’+ t.TABLE_NAME +’  ON’ + @CRLF+ @CRLF

FROM INFORMATION_SCHEMA.TABLES t

WHERE  t.TABLE_TYPE = ‘BASE TABLE’

AND t.TABLE_NAME LIKE @DimensionTableNamePrefix + ‘%’

SELECT @SQL= @SQL + ‘INSERT INTO ‘ + t.TABLE_SCHEMA +’.’+ t.TABLE_NAME + ‘ (‘ + @crlf

–{ column_list }

+ replace(stuff(( SELECT ‘,’ + c.COLUMN_NAME FROM

INFORMATION_SCHEMA.columns c

WHERE c.COLUMN_NAME = COLUMN_NAME AND c.TABLE_NAME = t.TABLE_NAME ORDER

BY

c.ORDINAL_POSITION FOR XML PATH(”) ), 1, 1, ”), ‘,’, ‘,’) + ‘) ‘ +

@crlf +

‘VALUES ‘

+ @crlf + ‘(‘

–{ VALUES }

+ replace(stuff(( SELECT ‘,”’ + CASE WHEN c.COLUMN_NAME LIKE ‘%’ +

@SurrogateKeyName + ‘%’ THEN @UnknownSurrogateKeyValue WHEN

CHARACTER_MAXIMUM_LENGTH > len(dt.DEFAULT_DESC) THEN dt.MAX_DESC WHEN

CHARACTER_MAXIMUM_LENGTH < len(dt.DEFAULT_DESC) THEN dt.MIN_DESC ELSE

DT.DEFAULT_DESC END + ”” FROM INFORMATION_SCHEMA.columns c INNER JOIN

@dataTypes dt ON

c.DATA_TYPE = dt.DATA_TYPE WHERE c.COLUMN_NAME = COLUMN_NAME AND

c.TABLE_NAME =

t.TABLE_NAME ORDER BY c.ORDINAL_POSITION FOR XML PATH(”) ), 1, 1, ”),

‘,’, ‘,’

) + ‘ ); ‘   + CHAR(13) + CHAR(13)

FROM   INFORMATION_SCHEMA.tables t

WHERE  t.TABLE_TYPE = ‘BASE TABLE’

AND t.TABLE_NAME LIKE @DimensionTableNamePrefix + ‘%’

SELECT @SQL = @SQL +  ‘SET  identity_INSERT ‘ + t.TABLE_SCHEMA +’.’+ t.TABLE_NAME +’  OFF’ + @CRLF+ @CRLF

FROM INFORMATION_SCHEMA.TABLES t

WHERE  t.TABLE_TYPE = ‘BASE TABLE’

AND t.TABLE_NAME LIKE @DimensionTableNamePrefix + ‘%’

PRINT @SQL

EXEC (@SQL)

END

Else

Print ‘Already Exists’

SSIS Screenshot:

Image

Posted in SSIS, T-SQL | Tagged | Leave a comment

PowerPivot Capacity Specification

PowerPivot for Excel is an add-in that you can use to perform powerful data analysis in Excel 2010, bringing self-service business intelligence to your desktop. PowerPivot for Excel includes a window for adding and preparing data, and a PowerPivot tab on the Excel ribbon that you can use to manipulate the data in an Excel worksheet.

The data that you work on in the PowerPivot window is stored in an analytical database inside the Excel workbook, and a powerful local engine loads, queries, and updates the data in that database. The PowerPivot data can be further enriched by creating relationships between the tables in the PowerPivot window. And because PowerPivot data is in Excel, it is immediately available to PivotTables, PivotCharts, and other features in Excel that you use to aggregate and interact with data. All data presentation and interactivity are provided by Excel 2010; and the PowerPivot data and Excel presentation objects are contained within the same workbook file.

In addition to the graphical tools that help you to analyze your data, PowerPivot includes Data Analysis Expressions (DAX). DAX is a new formula language that extends the data manipulation capabilities of Excel to enable more sophisticated and complex grouping, calculation, and analysis. The syntax of DAX formulas is very similar to that of Excel formulas, using a combination of functions, operators, and values.

Before start working on the PowerPivot you must familiar with the specifications:

PowerPivot Specifications

PowerPivot Specifications

Posted in PowerPivot | Leave a comment

How to restore PowerPivot excel file as a SSAS Database.

Basically PowerPivot stores the data inside the In memory analysis services (Vertipaq). You can restore the inside database into the SSAS tabular or SharePoint instance.
Following are the easy steps to get the inside database from the PowerPivot file:
1. Rename PowerPivot file to zip
2. Extract Zip file.
3. There are lot of folders and file which is another topic to discuss. But at this moment we will go inside the \xl\customData
4. CustomData folder will contains the item1.dat file which you need to change the extension to abf file. Abf extension required to restore it.
5. Then login into the SSAS which are running either tabular or as a SharePoint instance.
6. Then right click on the database and select restore.
7. Then specify the item1.abf file as the backup file.
8. Type the database name.
9. Don’t forget to press the OK button🙂.
10. Newly created database will appear after refreshing the node.

Note:- powerpivot only restore in tabular or sharepoint instance.

All done slice and dice as a normal OLAP database.

Posted in PowerPivot, SSAS | Leave a comment

What’s Inside an SSIS 2012 .ispac File?

If you’re curious, you can rename the .ispac file and give it a .zip extension, and extract. Voila, we see all the files that make up the ispac – a manifest, your DTSX files, and a .param file. Also note that the manifest does not have the usual long .ssisDeploymentManifest extension.

SSIS-2012-Whats-Inside-an-ISPAC-300x99

Posted in SSIS | Tagged | 2 Comments

Small Businesses Adoping Tablets in a Big Way

18820

Image | Posted on by | Tagged | Leave a comment

Self-Service BI using PowerPivot and Power View

A powerful solution that provides users of all levels with new insights through familiar Microsoft Office tools. Organizations can create compelling reports and analytical applications, easily share insights, and collaborate with colleagues.

See following presentation for detail about products overview:

3_Special-OPSTOUR-BreakthroughInsights

Posted in SQL Server features | Leave a comment