Advertisements

Archive

Archive for July 28, 2014

Self Service BI by using Power BI – Power Query (Part 1)

July 28, 2014 1 comment

In my previous posts I discussed about Power BI [link], what is it, its components, features and capabilities in the new world of Self Service IB.

Self Service BI allows end users to design & deploy their own reports, analyse within an approved & supported architecture and tools portfolios. End users do not have to worry about maintaining databases, doing integrations, creating warehouses/marts, reports, etc. The Self Service BI tool provide features which are capable enough to do all these activities in an automated, quick and efficient way, and all you have to do is learn how to configure these tools.

Power BI is one such tool offered by Microsoft, you can read about it my previous posts, [link] and Microsoft Official blog, [link].
 

Power BI works ONLY with Excel & Office 365. It is nothing but a collection of different components which provide features as follows:
1. Power Query
2. Power Pivot
3. Power View (aka Crescent)
4. Power Map (aka GeoFlow)
5. Power Q & A (aka Natural Language Processing)
6. Office 365
7. Windows 8 App
 

–> Power Query is used to easily discover or gather data from various public or corporate sources, like:

Web page SQL Server database IBM DB2 database Windows Azure Marketplace
Excel or CSV file Windows Azure SQL Database MySQL database Active Directory
XML file HDInsight SharePoint List Facebook
Text file Access database OData feed SAP BusinessObjects BI Universe
Folder Oracle database Hadoop Distributed File System (HDFS)

 

–> Let’s see a small demo how you can use Power Query: All you need is Excel and Power Query add-In, which you can download from Microsoft official site, [link].

Below image shows various ways you can access public or corporate data from various sources from an Excel workbook:
1. Online Search
2. From File
3. From Database
4. From Other Sources

PowerBI_PowerQuery00
 

Let’s see how Online Search works. The moment you type “Olympics 2014” it populates lot of sources from where you can fetch data from:

PowerBI_PowerQuery01
 

On selecting a source it tries to connect to the public portal and displays the Source URL while fetching the data:

PowerBI_PowerQuery02
 

And finally populates the data in tabular format in an Excel worksheet:

PowerBI_PowerQuery03
 

Go to Ribbon, click “Table Tools” -> Query -> Edit, this will open the “Query Editor” window where you can edit, clean the data. You can even Create Queries that you can save and use again later to refresh your data. Merge different tables in one step; rename, delete or even create fields. Transform your data before even importing it into a spreadsheet.

PowerBI_PowerQuery04
 

This way you can get your data ‘analysis ready’ with Power Query !!!


Advertisements

SQL Tip – Disable or Enable all Indexes of a table at once

July 28, 2014 Leave a comment

 
There are times when you need to DISABLE all Indexes of a table, like when there is a need to INSERT/UPDATE huge records in a table. As you have to INSERT huge records in a table the whole INSERT process could take more time to maintain/process Indexes on that table. Thus before inserting records its good to DISABLE all Non-Clustered indexes and post processing Re-Enable them.

USE [AdventureWorks2014]
GO

-- Disable Index
-- Syntax: ALTER INDEX [idx_name] ON [table_name] DISABLE;
ALTER INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person] DISABLE;
GO

-- Enable Index
-- Syntax: ALTER INDEX [idx_name] ON [table_name] REBUILD;
ALTER INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person] REBUILD;
GO

Please note: to Enable you need to use REBUILD option, there is no ENABLE option just like DISABLE in above DDL statements.
 

–> Generate Queries of ALTER DDL scripts to:

– Disable all Indexes:

SELECT 
	o.name, 
	'ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' 
		+ QUOTENAME(SCHEMA_NAME(o.[schema_id])) 
		+ '.' + QUOTENAME(o.name) + ' DISABLE;'
FROM sys.indexes i
INNER JOIN sys.objects o
ON o.object_id = i.object_id
WHERE o.is_ms_shipped = 0
AND i.index_id >= 1
AND o.name = 'Person'

 

– Enable all Indexes:

SELECT 
	o.name, 
	'ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' 
		+ QUOTENAME(SCHEMA_NAME(o.[schema_id])) 
		+ '.' + QUOTENAME(o.name) + ' REBUILD;'
FROM sys.indexes i
INNER JOIN sys.objects o
ON o.object_id = i.object_id
WHERE o.is_ms_shipped = 0
AND i.index_id >= 1
AND o.name = 'Person'