Archive

Author Archive

Top SQL Blogs – Windows 8 App | from MSDN, MVPs and Top Bloggers

March 14, 2014 Leave a comment

Dear Readers,

After my first App [SQL with Manoj] got published on Windows 8 Store, I thought to create an another App on a broader level, which will cover latest SQL Server (TSQL) topics from MSDN blogs, MVPs and other famous Bloggers, like Paul S. Randal, Kimberly Trip, Sam Lester, Brent Ozar, etc.

And today I’m very happy to announce the release of my 2nd Windows 8 App for and its availability on Windows 8 store.

Top SQL Blogs

With this App you can view and read SQL Server related topics from various sources, like MSDN blogs, MVPs and other famous Bloggers at one place.

Please download this App on your Windows 8 PC or Tablet and let me know your feedback.

Thanks!!!

PowerBI now GA | Intro & Resources


Power BI for Office 365 is now Generally Available, which is a cloud-based Business Intelligence service that gives people a powerful new way to work with data in the tools they use every day, Excel and Office 365. You can experience both Self-Service BI and Online Services by using Excel and Power BI for Office 365. Power BI delivers a new way to Integrate, Transform and Exploit data through Excel as well as new opportunities to access, Share and Search through Office 365. Power BI is a solution of a Workstation with Excel part and “App”, as well as an Online game with Office 365.

Power BI for Office 365 Overview

In my [previous] blog post I demonstrated the capabilities of Power BI Components by showing their Videos. Here in this post I’ll provide the Resources to setup Power BI environment for you.

>> To start you can Sign up for a Free Trial of Power BI for Office 365 on this site: http://www.microsoft.com/en-us/powerbi/default.aspx#fbid=v0V8xzuZXJR

>> Download components of Power BI integrated into Excel:
1. Power Query
2. Power Pivot
3. Power View
4. Power Map (formerly Geoflow )
5. Power BI Mobile App (or Mobile BI).

>> Online Components in Office 365:
6. BI Power for Office 365
7. Power Q&A
8. Data Management and Data Stewardship

As part of the Power BI trial you’ll also receive a free 30 day trial of Office 365 ProPlus giving you access to the latest version of Excel. Once you have Excel installed, download and install the Power Query and Power Map add-ins for Excel and learn about the new features with the getting started guide.


>> Power BI Offering Options:
1. Power BI, Add-on for E3/E4 Subscribers
2. Power BI, Standalone
3. Power BI, Standalone + Office 365 ProPlus

Check following link for “Power BI” current pricing: http://www.microsoft.com/en-us/powerBI/pricing.aspx?WT.mc_id=blog_PBI_GA_PBI#fbid=v0V8xzuZXJR

Power BI for Office 365 | Self Service BI in Excel

March 4, 2014 1 comment

Microsoft Power BI for Office 365 is a collection of features and services that enable you to Visualize data, share Discoveries, and Collaborate in intuitive new ways. Microsoft Power BI for Office 365 provides an Organization-wide Self-service Business Intelligence (BI) infrastructure, and brings Excel workbook sharing, Online collaboration, and IT infrastructure together into a holistic offering.

Power BI consists of many features and services, and works seamlessly with Excel. Use Excel to create compelling content, data models, and visualizations, and then use Power BI to share, collaborate, and extend those insights

>> Self-Service BI Features in Excel: these features extend the data-specific features and capabilities of Excel 2013:
1. Power Query – Discover: easily discover and connect to data from public and corporate data sources

2. Power Pivot – Analyze: create a sophisticated Data Model directly in Excel

3. Power View – Visualize: create reports and analytical views with interactive data visualizations
4. Power Map – Visualize: explore and navigate geospatial data on a 3D map experience in Excel

>> Power BI for Office 365: these features amplify the self-service BI capabilities created in Excel by making them available in a collaborative online environment:
5. Power BI Sites – Share Data & Insights: Share, View, and Interact with reports in these collaborative Power BI sites

6. Power BI Q&A – Get Answers: use natural language queries to find, explore, and report over your data

7. Query and Data Management – share and manage queries and data sources, and view query usage analytics
8. Power BI Windows Store App – Access Data Anywhere: view reports on the go, with the Power BI app

>> IT Infrastructure Services for Power BI: these features get Power BI running in your organization, and enable IT professionals to provide, manage, and secure Power BI services:
9. Provision Power BI for Office 365 – create a tenant, and get Power BI for your organization
10. Power BI Admin Center – manage your organization’s Power BI infrastructure from a site
11. Data Management Gateway – connect on-premises data to the cloud, seamlessly and securely


Power BI Provisioning Guide: http://go.microsoft.com/fwlink/?LinkID=317870

Power BI Getting Started Guide: http://office.microsoft.com/en-001/office365-sharepoint-online-enterprise-help/redir/HA104103589.aspx?CTT=5&origin=HA104103581

Power BI MSDN Blog: http://blogs.msdn.com/b/powerbi/


Learn More: http://office.microsoft.com/en-001/office365-sharepoint-online-enterprise-help/power-bi-for-office-365-overview-and-learning-HA104103581.aspx

2013 in review

December 31, 2013 Leave a comment

The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 210,000 times in 2013. If it were an exhibit at the Louvre Museum, it would take about 9 days for that many people to see it.

Click here to see the complete report.

Memory Optimized Indexes | Hash vs Range Indexes

December 20, 2013 3 comments

In SQL Server 2014 for In-Memory tables there are lot of changes in DDLs compared with normal Disk Based Tables. In-Memory Tables related changes we’ve seen in previous posts, check [here]. Here we will see Memory Optimized Index related changes and few important things to take care before designing your Tables and Indexes.

–> Some of the main points to note are:

>> Indexes on In-Memory tables must be created inline with CREATE TABLE DDL script only.

>> These Indexes are not persisted on Disk and reside only in memory, thus they are not logged. As these Indexes are not persistent so they are re-created whenever SQL Server is restarted. Thus In-Memory tables DO NOT support Clustered Indexes.

>> Only two types of Indexes can be created on In-Memory tables, i.e. Non Clustered Hash Index and Non Clustered Index (aka Range Index). So there is no bookmark lookup.

>> These Non Clustered Indexes are inherently Covering, and all columns are automatically INCLUDED in the Index.

>> Total there can be MAX 8 Non Clustered Indexes created on an In-Memory table.

–> Here we will see how Query Optimizer uses Hash & Range Indexes to process query and return results:

1. Hash Indexes: are used for Point Lookups or Seeks. Are optimized for index seeks on equality predicates and also support full index scans. Thus these will only perform better when the predicate clause contains only equality predicate (=).

2. Range Indexes: are used for Range Scans and Ordered Scans. Are optimized for index scans on inequality predicates, such as greater than or less than, as well as sort order. Thus these will only preform better when the predicate clause contains only inequality predicates (>, <, =, BETWEEN).

–> Let’s check this by some hands-on code. We will create 2 similar In-Memory tables, one with Range Index and another with Hash Index:

-- Create In-Memory Table with simple NonClustered Index (a.k.a Range Index):
CREATE TABLE dbo.MemOptTable_With_NC_Range_Index
(
    ID INT NOT NULL
        PRIMARY KEY NONCLUSTERED,
    VarString VARCHAR(200) NOT NULL, 
    DateAdded DATETIME NOT NULL
) WITH (
	MEMORY_OPTIMIZED = ON, 
	DURABILITY = SCHEMA_AND_DATA
)
GO

-- Create In-Memory Table with NonClustered Hash Index:
CREATE TABLE dbo.MemOptTable_With_NC_Hash_Index
(
    ID INT NOT NULL
        PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000),
    VarString VARCHAR(200) NOT NULL, 
    DateAdded DATETIME NOT NULL
) WITH (
	MEMORY_OPTIMIZED = ON, 
	DURABILITY = SCHEMA_AND_DATA
)
GO

–> Now we will Insert about 10k records on both the tables, so that we have good numbers of records to compare:

DECLARE @i INT = 1

WHILE @i <= 10000
BEGIN
	INSERT INTO dbo.MemOptTable_With_NC_Range_Index 
		VALUES(@i, REPLICATE('a', 200), GETDATE())

	INSERT INTO dbo.MemOptTable_With_NC_Hash_Index 
		VALUES(@i, REPLICATE('a', 200), GETDATE())

	SET @i = @i+1
END

–> Now check the Execution Plan by using equality Operator (=) on both the tables:

SELECT * FROM MemOptTable_With_NC_Hash_Index  WHERE ID = 5000 -- 4%
SELECT * FROM MemOptTable_With_NC_Range_Index WHERE ID = 5000 -- 96%

You will see in the Execution Plan image below that Equality Operator with Hash Index Costs you only 4%, but Range Index Costs you 96%.
SQLServer2014_Hash_vs_Range_1

–> Now check the Execution Plan by using inequality Operator (BETWEEN) on both the tables:

SELECT * FROM MemOptTable_With_NC_Hash_Index  WHERE ID BETWEEN 5000 AND 6000 -- 99%
SELECT * FROM MemOptTable_With_NC_Range_Index WHERE ID BETWEEN 5000 AND 6000 -- 1%

You will see in the Execution Plan image below that Inequality Operator with Range Index Costs you only 1%, but Hash Index Costs you 99%.
SQLServer2014_Hash_vs_Range_2

So, while designing In-Memory Tables and Memory Optimized Indexes you will need to see in future that how you will be going to query that table. It also depends upon various scenarios and conditions, so always keep note of these things in advance while designing your In-Memory Tables.

Follow

Get every new post delivered to your Inbox.

Join 379 other followers