Power Pivot and Power View now available in Excel 2013.

In Excel 2013, much of that functionality, including the infrastructure that supports it, is built directly into the Data Model in Excel. Without installing a separate add-in, PowerPivot and Power View add-ins are installed by default. But they are not automatically enabled.  You have to do some menu/dialog box acrobatics to turn them on, but it’s a one-time set-and-forget requirement. 

If you’d like to try it yourself, click through File|Options|Add-Ins|Manage COM Add-Ins|Go… and then enable the add-ins by checking them off and clicking OK.

Below diagram will give you more idea.

Some of the Excel 2013 Features:

If you are looking to quickly build out a self-service BI solution that tells the story of your data, you can now use the tool you know and love – Excel - to rapidly mash-up, explore, analyze and visualize any data ranging from a few rows to hundreds of millions of rows.

·        xVelocity in-memory analytics:

Analyze data ranging from a few rows to hundreds of millions of rows instantly on your desktop using Excel data models, new in 2013 powered by xVelocity in-memory analytics engine. More Details

·        Quick Explore, Trend Charts, Quick Analysis and Flash Fill:

Improve productivity by easily shaping your data with Flash Fill; using Quick Analysis to preview and apply conditional formatting, suggest and create charts, PivotTables, and tables; and using Quick Explore to easily navigate multidimensional and tabular data models and create Trend charts to analyze information over time. 

·        Power Pivot:

Access, mash-up and analyze data from virtually any source and rapidly create compelling analytical models with PowerPivot, now in Excel.

·        Power View:

Provide stunning data visualization to discover new insights with a highly interactive and familiar data exploration, visualization, and presentation experience with Power View, now in Excel. More Details

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)

Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Received Microsoft Community Contributor Award

I’ve received a mail from Microsoft and this is what mail was said

Dear Suhas,

Congratulations! We’re pleased to inform you that your contributions to Microsoft online technical communities have been recognized with the Microsoft Community Contributor Award.

The Microsoft Community Contributor Award is reserved for participants who have made notable contributions in Microsoft online community forums such as TechNet, MSDN and Answers. The value of these resources is greatly enhanced by participants like you, who voluntarily contribute your time and energy to improve the online community experience for others.

Becoming a Microsoft Community Contributor Award recipient includes access to important benefits, such as complimentary resources to support you in your commitment to Microsoft online communities. To find out more about the Microsoft Community Contributor Award and to claim your recognition, please visit this site: http://www.microsoftcommunitycontributor.com/

Through the Microsoft® Community Contributor Award, Microsoft expresses their thanks to individuals who freely volunteer their time and energy to help improve the Microsoft online technical community experience for others.

A Big Thanks to Microsoft for the Recognition! Thanks’ to whoever nominated me and everyone who helped me to reach to this point and I will try my best as much as I can.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)

Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

VertiPaq Engine Renamed as xVelocity in SQL Server 2012.

VertiPaq engine is currently only available to PowerPivot for Excel in SQL Server 2008 R2. After the amazing success of PowerPivot for Excel in the previous release of SQL Server 2008 R2 this engine moved to the server side with the same InMemory BI and compression and it’s called the VertiPaq Engine or the Analysis Services Tabular Mode. Data retrieval and calculations happen at a much faster rate as its entire database is in-memory.Vertipaq enables Excel to process hundreds of millions of rows with sub-second response times on desktop hardware.

Now Along with the SQL Server 2012 launch, Microsoft announced xVelocity in-memory technologies, delivering huge performance improvements for data warehousing and business intelligence. This engine uses in-memory column-oriented storage and innovative compression techniques to achieve these remarkable results.

xVelocity is not a new feature, but rather a renaming of an existing feature.  Vertipaq is the existing feature that will now be called xVelocity.  So the Vertipaq engine that is inside PowerPivot and Analysis Services 2012 Tabular is now called “xVelocity in-memory analytics engine”.  Also, “xVelocity” now refers to the column store index feature in the SQL Server 2012 relational database.

Columnar database is its driving principle So First Column bases storage as opposed to “row store” & Secound High level of compression, all in memory – RAM based. In SQL Server 2012, Microsoft has taken the same Vertipaq engine and integrated it into Analysis Services. The results have been staggering with scan rates up to 10s of billions of rows per second on typical server hardware. 

Using a combination of compression, algorithms enhanced for modern CPU/memory architecture, and highly parallel intra-query execution, xVelocity can speed up query execution times by 4x, 10x, and even 100x, simply by adding column store indexes to the fact tables of a star-schema data warehouse.

Get More details.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)

Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

BIDS No More Exist in SQL Server 2012.

Once you will installed SQL Server 2012 you could not find the BIDS to develop SSAS/SSIS/SSRS application. BIDS will replace with SSDT i.e. SQL Server Data Tools.

Comparison of SQL Server 2012 Vs SQL Server 2008 R2.

You can get more idea on below Screen shot how SQL Server 2012 looks compare with SQL Server 2008 R2 components.

SQL Server 2012 Components

1. SQL Server Data Tools
2. SQL Server Management Studio
3. Analysis SErvices
4. Configuration Tools
5. Data Quality Services
6. Documentation & Community
7. Integration Services
8. Master Data Services
9. Performance Tools

So in SQL Server 2012 we have below four new components available.

1. SQL Server Data Tools
2. Data Quality Services
3. Documentation & Community
4. Master Data Services

Today will discuss more on SSDT i.e. SQL Server Data Tools.

SSDT (SQL Server Data Tools)

1-      Developing SSAS/SSIS/SSRS Projects.

2-      Developing Databases in Visual Studio like working in SQL Server Management studio.

Will discuss the how we can use SSDT one by one.

Developing SSAS/SSIS/SSRS Projects.

·         If you are looking for BIDS (Business Intelligence Development Studio) in SQL Server 2012, then you will not find it. It is replaced with SQL Server Data Tools (SSDT) in SQL Server 2012.

·         You can start the SSRS/SSAS/SSIS Projects as we created in BIDS in SQL Server 2008/R2.

·         SQL Server 2012 installs "SQL Server Data Tools" under the "SQL Server 2012" Start Menu option - this includes BIDS but not Database Projects.

·         In this use can create the SSAS Tabular Projects called as BISM.

Developing Databases in Visual Studio like working in SQL Server Management studio.

·         In this you can create new Database Design features with schema management etc in Visual Studio more like working in SQL Server Management studio.

·         The idea is that developers want to develop databases using similar methods to working with regular (i.e. .NET code) object within VS.  To that end, SSDT includes offline projects, object creation interfaces (i.e. for tables, views, etc…) schema snapshot and compare, database version targeting (for SQL Server 2005, 2008, 2012 and for SQL Azure) and refactoring with preview.

·         Database Projects installs "MICROSOFT SQL Server Data Tools" under the root of the Start Menu - this doesn't have BIDS functionality included if installed separately.

 

Features

1. Responsive SSDT'S GUI as compared to SQL Server Management Studio.
2. Fast and reliable build of deployment scripts.
3. Detection of isolated changes in the target database.
4. Resolve references to other production and system databases like msdb & databases connected by a linked server.
5. Support of migration scripts to enable data preservation and to add static data.
6. Possibility of executing and deploying a single file.
7. Comfortable T-SQL writing.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)

Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

SQL+MDX in one Apartment (Hybrid Query).

While replying on the MSDN Forums Most of the time people ask on how to write SQL+MDX both in single place means.

1-      Certain data like aggregated data from MDX Query.

2-      Select that data from SQL Query and get these data in table to show on reports.

I already answer couple of the question on forums but then think it’s better to write blog on above topics.

Some for the forum post I Included here for reference.

1-      SSRS combining MDX and T-SQL.

2-      Need help on MDX query to retrieve data from linked table.

3-      MDX Named Set and OPENQUERY.

4-      The OLE DB provider "MSOLAP" for linked server "Linked Server Name" supplied inconsistent metadata for a column.

How to Create SQL+MDX-

Basically to write SQL+MDX combined we have to create link server from where we want data to combine with SQL. To create link server you need to have Admin access to the SQL Server.

IF IS_SRVROLEMEMBER ('sysadmin') = 1

   print 'Current user''s login is a member of the sysadmin role'

ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0

   print 'Current user''s login is NOT a member of the sysadmin role'

ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL

   print 'ERROR: The server role specified is not valid.'

Above Query returns the whether you have admin admittance to the server or not to create Link Server.

More Details on IS_SRVROLEMEMBER (Transact-SQL)

Suppose we have access to the Link server now to get data from created linked server we use another function named as OPENQUERY – Basically OpenQuery function Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

More Details on OPENQUERY (Transact-SQL)

More Details on Hybrid Query-

USE

AdventureWorksDW2008R2

GO

EXEC

sp_addlinkedserver

@server='Test',

@srvproduct='',

@provider='MSOLAP',

@datasrc='localhost',

@catalog='Adventure Works DW 2008R2'

Declare @MDXExpression as Varchar(MAX)

Select @MDXExpression =

'

SELECT

NON EMPTY

      {

      [Measures].[Sales Amount]

      ,[Measures].[Standard Product Cost]

      ,[Measures].[Tax Amount]

      ,[Measures].[Total Product Cost]

      } ON 0,

NON EMPTY

      {

      [Sales Channel].[Sales Channel].[Sales Channel]

      } ON 1

FROM

      [Adventure Works]

';     

Exec ('SELECT * INTO ##TestTemp FROM OpenQuery(TestTest1,''' + @MDXExpression + ''')')

SELECT

      CONVERT(varchar,t."[Sales Channel].[Sales Channel].[Sales Channel].[MEMBER_CAPTION]") AS [Date],

      (CONVERT(nvarchar,t."[Measures].[Sales Amount]")) AS [Sales Amount],

      (CONVERT(nvarchar,t."[Measures].[Standard Product Cost]")) AS [Standard Product Cost],

      (CONVERT(nvarchar,t."[Measures].[Tax Amount]")) AS [Tax Amount],

      (CONVERT(nvarchar,t."[Measures].[Total Product Cost]")) AS [Total Product Cost]

from

      ##TestTemp t

--DRop Table ##TestTemp

Query Wxecution-

1-      So above query first creates liked server with named as 'Test'.

2-      After that MDX query executed on linked server where we have kept the MDX query in MDXExpression variable.

3-      Using OpenQuery function data gets dump in to ##TestTemp table.

4-      Finally we get the data from ##TestTemp table.

SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)

Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Have a happy and prosperous New Year 2012

This has been an exciting year for me I achieved lots of thing in this year and continue to achieve in next year. Thanks every one for your support.

Have a happy and prosperous New Year 2012 from me and my family to all my friends Blog readers.

May the blessings of the Lord enrich your family and friends, not only in wealth but in health, love and happiness.

A New Year starts, with a new calendar.  But my love stays constant, with you always in my heart.  Happy New Year 2012!

Thanks,
Suhas Rajaram Kudekar

Available MDX Time Functions in SSAS.

Today I will discuss more on available MDX Time Functions. I already post the number of available MDX function in my previous post (Types of MDX Functions available in MDX Queries).

For those who doesn’t know more on MDX. Those can get some more idea on my previous post (To get a comma separated list in SQL & MDX).

There are total 9 Time Function available in MDX & MSBI developer people like to use them in their reports.

1-Function Name:  ClosingPeriod

Syntax:  CLOSINGPERIOD( [«Level»[, «Member»] ] )

Description:  The ClosingPeriod function returns the last sibling from the descendants of a supplied member at a supplied level. 

The query above returns the default measure for the closing period at the Calendar Month level for the member CY 2008.

2-Function Name:  LastPeriods

Syntax:  LASTPERIODS( «Index»[, «Member»] )

Description:  The LastPeriods function returns the supplied number of members up to and including the supplied member.

The above query returns the first 15 days of July 2005 in the Calendar hierarchy in the Date dimension.

3-Function Name:  Mtd

Syntax:  MTD( [«Member»] )

Description:  The MTD function returns all siblings of the supplied member starting at the first sibling and ending with the supplied sibling.

The above query returns all siblings up to and including the member July 8, 2005 from the Calendar hierarchy in the Date dimension.

4-Function Name:  OpeningPeriod

Syntax:  OPENINGPERIOD( [«Level»[, «Member»] ] )

Description:  The OpeningPeriod function returns the first sibling from the descendants of a supplied member at a supplied level. 

The query above returns the default measure for the opening period at the Calendar Month level for the member CY 2008.

5-Function Name:  ParallelPeriod

Syntax:  PARALLELPERIOD( [«Level»[, «Numeric Expression»[, «Member»] ] ] )

Description:  The ParallelPeriod function returns a member from a prior period in the same relative position as a supplied member. 

The query above returns the member Q2 CY 2008 which is at the supplied Calendar Quarter level (Q1 CY 2004) and three prior periods (Calendar Year) away from the supplied member of Q1 CY 2004.

6-Function Name:  PeriodsToDate

Syntax:  PERIODSTODATE( [«Level»[, «Member»] ] )

Description:  The PeriodsToDate function returns a set of siblings from the same level as the supplied member. The returned set starts with the first sibling and ends with the supplied member.

The query above returns the first sibling at the Date level up to the supplied member of March 7, 2008. 

7-Function Name:  Qtd

Syntax:  QTD( [«Member»] )

Description:  The QTD function returns a set of siblings from the same level as the supplied level. The set consists of the first sibling up to and including the supplied member.

The query below returns a set of dates starting with the first day of the quarter (July 1, 2005) and ending with supplied date of July 07, 2005.

8-Function Name:  Wtd

Syntax:  WTD( [«Member»] )

Description:  The WTD function returns a set of siblings from the same level as the supplied level. The set consists of the first sibling up to and including the supplied member.

If you run the above statement in SQL Server Management Studio, you would receive the following error: Executing the query ... Query (1, 8) By default, a week level was expected. No such level was found in the cube. Execution complete.

9-Function Name:  Ytd

Syntax:  YTD( [«Member»] )

Description:  The YTD function returns a set of siblings from the same level as the supplied level. The set consists of the first sibling up to and including the supplied member.

The query above returns a set of dates starting with July, 2005 and ending with July 14, 2005.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)

Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

'SQL Server Denali' No More: SQL Server 2012 Announced.

Wish you Happy Diwali and New Year to all of my friends & there family.

Now some important announced of SQL Server.

Microsoft VP Ted Kummert announced the official name of the next version of the SQL Server – SQL Server 2012. The version of the SQL Server will be 11. The release date is estimated in the first half of the year 2012.

Microsoft also announced that the new BI self-service reporting tool, codename “Crescent”, (Get more details of Crescent in my previous blog post) will be given the official name Power View. Power View (Crescent) is a highly interactive data exploration tool and a preview is already available with the CTP3 of Denali.

SQL Server 2012 Power View (Crescent) is one of the new tools that will be released with the next version of SQL Server. Currently it is available in the SQL Server Denali CTP, the beta version of the next SQL Server, SQL Server 2012.

The business user will start the application by opening an existing Crescent report or choosing a BISM – Business Intelligence Semantic Model (Get more details of BISM in my previous blog post) to start from. This BI Semantic Model is also a new feature in the Denali release. As the name says, it is a semantic model/layer on top of your data warehouse. Using this model abstracts the data warehouse complexity for a business user, and enables him/her to create reports with Crescent.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)

Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Change the Look & Fill of the Report Manager.

In Previous Blog Architecture Diagram of SSRS  post we were getting the completed diagrammatic overview of SSRS Architecture. In that a Web-based report access and management tool is available named as Report Manager.

Definition - Report Manager is a Web-based report access and management tool that we use to administer a single report server instance from a remote location over an HTTP connection. we can also use Report Manager for its report viewer and navigation features.

Different type of Report Manager Properties Available on clicking on diffrent available Tab.

·         Site Settings Properties

On the Site Settings page, we have General Properties, Security and Schedule

Report Manager – Data Source Properties with the exception of Dependent Items. Dependent Items page will list all items (reports, data sets) that use this particular Data Source.

·         Search Properties

We can now search in SSRS Report Manager! Type in our keyword, and a way we go! It will find your report, your data set, your folder, or even your report part.

·         Report Contextual Dropdown Properties

When we hover over a report item and click on the down arrow, we will be able to move, delete, edit in Report Builder 3.0, and explore other properties, to name a few.

·         Report Properties

We have links for : properties, data sources, shared data sets, subscriptions, processing options, cache refresh options, report history, snapshot, and security.

·        Shared Data Set Properties

Share data sets among diffrent reports.

·         Shared Data Set Caching Properties

Cache these shared data sets.

We can use Report Manager to browse the report server folders or search for specific reports. we can view a report, its general properties, and past copies of the report that are captured in report history. Depending on your permissions, we might also be able to subscribe to reports for delivery to an e-mail inbox or a shared folder on the file system.

How we can Start Report Manager

ü  To start Report Manager from a browser Open Microsoft Internet Explore

ü  In the address bar of the Web browser, type the Report Manager URL.

By default, the URL is http://<ComputerName>/reports.

ü  The report server might be configured to use a specific port. For example,

http:// <ComputerName>:80/reports or

http:// <ComputerName>:8080/reports.

Change Bascic look & fill of Report Manager 

To change the top area color in Report Manager, please refer to the following steps to achieve this:

1.      Navigate to ReportingServices.css file.

Path: <Drive:> \Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\Styles

2.      If want backup the ReportingServices.css file before you modify it, open it with notepad format.

3.      Change the following portion in ReportingServices.css file:

Original Available - table.msrs-header { background-color:#fff; }

After Changed - table.msrs-header { background-color: #f00; }

This way we can change the Look & Fill of the Report Manager.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)

Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2