Watch & Subscribe my SQL videos on YouTube | Join me on Facebook

PowerBI now GA | Intro & Resources

March 6, 2014 Leave a comment

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

Categories: Power BI Tags: , , , , , , ,

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

Categories: Power BI Tags: , , , , , , ,

Cannot use Temp Table and Table Variable in Views, why? – MSDN TSQL forum

February 14, 2014 Leave a comment

–> Question:

I Know we cannot use Temp table, Table variable in View, but I want to know the reason behind it?
 

–> My Answer:

Because View is nothing but a simple stored query. A View definition just contains one single query, which can pull data from one or many tables by using JOINS. Every time a View is executed it is expanded upto the Query level and that Query is executed by the SQL Engine internally every time.

It is not like Stored Procedures where you have bunch of queries with control flow with loops, and you create temp-tables/variable for temporary storage. If you would like to add temp data use Stored Procedures instead of Views.

Views are just used to simplify complex queries usage, and saves us to re-write same code/query again and again.
 

–> Another Answer by Oalf:

a local temporary table is only available in the session where it has been created and only as long as the session exists, as soon as you close the session the temp table will be automatically dropped; so how should it work, creating a view on a temp table which exists for only a may very short period and at all, what should that be good for?
 

–> Another Answer by Erland:

There is certainly a good reason for views not supporting table variables. Table variables are only visible in the batch they are created in, and the CREATE VIEW statement must be in a batch of its own, so the table variable never exist when the CREATE VIEW statement runs.

For temp tables, it would certainly be possible. The view would become invalid as soon the scope where the temp table is creates is exited, but then again tables can be dropped to. However, there are some interesting questions.
 

Ref Link.

Categories: SQL Server Questions Tags: ,

SQL Trivia – Find second (or nth) Highest Salary or Marks

January 5, 2014 Leave a comment

At a beginner level this is the most basic and widely asked SQL Interview Question, and I’ve seen many people talk about this when you ask them to ask a basic SQL question.

Or, I could say it’s a tollgate to judge if a candidate knows basic SQL or not 🙂

So, here on demand of lot of people who ping me or email me for this, I’ve drafted this post on various ways or Queries to get the desired results.
 

–> I’ll create a dummy dbo.Employee table and populate with some dummy records (all these are my school/college friends):

USE tempdb
GO

CREATE TABLE dbo.Employee (
	EmpID INT PRIMARY KEY NOT NULL,
	EmpName VARCHAR(100),
	Salary Money
)
GO

INSERT INTO dbo.Employee
SELECT 1,  'Manoj',   20000
UNION ALL
SELECT 2,  'Saurabh', 50000
UNION ALL
SELECT 3,  'Kanchan', 30000
UNION ALL
SELECT 4,  'Hema',    10000
UNION ALL
SELECT 5,  'Bhanu',   60000
UNION ALL
SELECT 6,  'Prakash', 80000
UNION ALL
SELECT 7,  'Vivek',   40000
UNION ALL
SELECT 8,  'Ramesh',  55000
UNION ALL
SELECT 9,  'Kapil',   65000
UNION ALL
SELECT 10, 'Dev',     90000
GO

select * from dbo.Employee order by Salary DESC
GO

 

–> We will check for these conditions, 2nd highest & nth highest salary records from the above query, as shown below:

2nd highest is: 6, Prakash, 80000.00
5th highest is: 8, Ramesh,  55000.00
7th highest is: 7, Vivek,   40000.00

 

–> 2nd Highest Salary:

1. For SQL Server 2005 and +

;WITH CTE AS (
SELECT 
	EmpID, EmpName, Salary, 
	ROW_NUMBER() OVER(ORDER BY Salary DESC) as RN
FROM dbo.Employee
)
SELECT EmpID, EmpName, Salary
FROM CTE
WHERE RN = 2
GO

2. For SQL Server 2000

SELECT TOP 1 EmpID, EmpName, Salary
FROM (SELECT TOP 2 EmpID, EmpName, Salary 
	  FROM dbo.Employees 
	  ORDER BY Salary DESC
) X
ORDER BY Salary ASC
GO

 

–> nth Highest Salary: Just parametrize the numbers

1. For SQL Server 2005 +

DECLARE @n INT = 5

;WITH CTE AS (
SELECT 
	EmpID, EmpName, Salary, 
	ROW_NUMBER() OVER(ORDER BY Salary DESC) as RN
FROM dbo.Employee
)
SELECT EmpID, EmpName, Salary
FROM CTE
WHERE RN = @n
GO

2. For SQL Server 2000

DECLARE @n INT = 5

SELECT TOP 1 EmpID, EmpName, Salary
FROM (SELECT TOP (@n) EmpID, EmpName, Salary 
	  FROM dbo.Employees 
	  ORDER BY Salary DESC
) X
ORDER BY Salary ASC
GO

--// Final Cleanup
DROP TABLE dbo.Employee
GO

 

There are lot other ways also to get the same results and you can come up with different Queries with more optimizations.

Please do let me know if you have any comments and suggestions, thanks!

Categories: SQL Trivia Tags: , ,

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.

Categories: SQLwithManoj Tags: ,