Monday, May 30, 2011

Architecture Diagram of SSAS/SSRS/SSIS Part - 1

Below is the diagrammatically representation of MSBI components

1- SSAS (SQL Server Analysis Services) Architecture Diagram

2- SSAS (SQL Server Integration Services) Architecture Diagram 


3- SSAS (SQL Server Reporting Services) Architecture Diagram

Next Part we can discuss one by one

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2 

Wednesday, May 18, 2011

Business Intelligence Semantic Model (BISM)

Analysis Services cubes as we know them today and SSAS+MDX are dead

Microsoft announce in November 2010. Very disappointed because from last 2+ years I am working on SSAS and loving this technology always try new things in SSAS.  

Like me all MSBI Developers now thinking to stay with SSAS+MDX Queries or go with other
Data warehousing technology so from couple of day reading new things.

But Its big reprieve for all those ware worried,  ;-) ;-) ;-)

Microsoft announce the road map for SSAS and good news comes for all the MSBI Developer that SSAS+MDX is Alive with “Denali” as Business Intelligence Semantic Model (BISM) is being introduced in Analysis Services.

What introductions coming in SQL Server “Denali” and the new class of BI applications and update on Analysis Services – Idea & Guideline as Below.

1-It is the foundation of the Microsoft BI stack, serving as the semantic model for Microsoft BI tools including Excel, SharePoint Insights and Reporting Services.

2-This will do this by embracing the relational data model and bringing it together with the multidimensional model under a single unified BI platform – providing the best of both worlds.

3-Business Intelligence Semantic Model is being introduced in Analysis Services for SQL Server “Denali” with the goal to fulfill this vision. Combination of
·         First multidimensional modeling which is capable of tackling the most advanced of BI applications
·         Second one the other hand, the relational data model is widely understood and accepted by developers and IT professionals around the world.

4-When you upgrade your Analysis Services project or server to SQL Server “Denali”, every cube automatically becomes a BI Semantic Model.

5- All client tools in the Microsoft BI stack – Excel, PowerPivot, SharePoint Insights and Reporting Services operate on this model.

6- MSBI professionals can create the model in Visual Studio and deploy it to an Analysis Services server.  Interestingly, the model can also be created by business users with PowerPivot for Excel and shared via PowerPivot for SharePoint.

7-PowerPivot and Analysis Services are two different products but both use the BI Semantic Model under the covers.  
·         PowerPivot - Targets business users and In the case of PowerPivot, the model is embedded inside an Excel workbook
·         Analysis Services - Targets BI professionals and IT and In the case of Analysis Services, the model is stored and managed on the server.

8-UDM (Unified Dimensional Model) is no longer used with SQL Server “Denali” and is subsumed by the BI Semantic Model.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Monday, May 16, 2011

Error: 4064 – Cannot open user default database. Login failed. Login failed for user…!

This Type of error will occur while you try to connect Sql Server Management studio to access database. You can use the below steps to successfully connect the Sql Server Management studio.

·         Open your Sql Server Management studio.

·         Now you will get the Login option where you need to click on the Options button

·         Click the connection properties tab if it is not active.

·         Click on the Connect to database: dropdown

·         Type in the name of a database that still exists. May master database name need to put.

·         Connect the Sql Server Management studio its working fine

After login we can change/Alter the default database name with command as below.

           ALTER LOGIN [User] WITH DEFAULT_DATABASE = master

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Monday, May 9, 2011

What tools can we used for OLAP Browser?

What is OLAP?
OLAP (On Line Analytical Processing) performs multidimensional analysis of business data and provides the capability for complex calculations, trend analysis, and sophisticated data modeling. It is quickly becoming the fundamental foundation for Intelligent Solutions including Business Performance Management, Planning, Budgeting, Forecasting, Financial Reporting, Analysis, Simulation Models, Knowledge Discovery, and Data Warehouse Reporting.

It can also be defined as the capability of manipulating and analyzing data from multiple perspectives is widely used in business intelligence, data mining and reporting applications that provide complex analysis of data in a short period of time.

What makes OLAP such a powerful data analysis tool, and why would you want to use it are question that most people ponder when they are first introduced to this technology.
Using OLAP lets you find out:
·         Why one month’s sales were better than another month’s sales?
·         Which product your Sales department selling the most of?
OLAP types can be found in the MOLAP, ROLAP, and HOLAP

Now our main Question is “What tools can we used for OLAP Browser”
Below are the tools for OLAP web browser may be some of miss in the list
·         Dundas
·         BI Companion
·         IntelliMax
·         Panorama
·         Radar-Soft
·         ProClarity
·         Ranet OLAP
·         PerformancePoint

Today we discuss more on Ranet OLAP Browser
Ranet OLAP is an open source set of data visualization controls – powered by Microsoft Silverlight and was written specifically for SQL Server Analysis Services with support for SQL Server. Ranet OLAP is our advanced front-end solution for Microsoft SQL Server Analysis Services.

It can be used for displaying data using most popular Web-browsers: Mozilla Firefox, Google Chrome, and Internet Explorer.

It provides parsing, generating and visualization of MDX language and MDX query results. Moreover it provides data input and Write back to OLAP cube. It works under .NET and Silverlight.
It includes:
-         MDX Document Object Model (MDX DOM)
-         MDX Parser
-         MDX Generator
-         Description of supported MDX Syntax
-         Set of OLAP visual controls for MS Analysis Services:
-         Dynamic Pivot Grid - Pivot Table with WriteBack ability for arbitrary MDX text query
-         MDX Query Designer - visual MDX query builder
-         OLAP metadata explorer
-         Choice controls: CubeChoice, KpiChoice,  MeasureChoice,  LevelChoice,  MemberChoice, etc

Ranet OLAP includes Visual Controls:

·         PIVOT GRID
-          Control shows a summary table which is a result of running the MDX query
-          Sorting – Ascending and Descending sorting can be applied to a row area, column area, or data area in the pivot grid table
-          Members group – grouping data in the row area and column area are in the pivot grid table
-          Show Properties – It can display the property of the dimension member in the table
-          Properties, Custom Properties – show properties of the dimension member or cell, and their current values, in a special window
-          DrillThrought – shows the detailed data used for calculating the indicator value for the cell, in a special window
-          Show MDX – displays the MDX query generated as a result of design, or prepared manually by the user, in a special window
-          Writeback – Editing cube data

·         MDX DESIGNER  
-          Control permits creating interactive reports based on Microsoft Analysis Services OLAP cube data
-          Customer Calculation Editor – a built-in Customer Calculations Editor permits the user to create calculated members and calculated sets on his own
·         Change Filter – to modify the filter, where a list of members included in the report is
·         MEMBER CHOICE – control serves for selecting one or a number of cube dimension members
·         Find tab – It is a visual element used to determine the search parameters
·         LEVEL CHOICE – control is used for selecting a level in the cube hierarchy.
·         MEASURE CHOICE – control is intended for selecting a cube measure, including selection within a specific measure group

·         KPI CHOICE – control is intended for selecting a Key Performance Indicator (KPI) for the cube
·         DATE PARAMETER – control is intended for selecting a date from a cube time dimension on a calendar form
·         KPI VIEWER – control shows Key Performance Indicators (KPI) defined in the cube, and their basic values at certain time

Why is Ranet OLAP your complete solution OLAP Browser?
ü  Interactive Designer Pivot tables.
ü  Support Functions Drill Down / Up And Drill Through.
ü  Flexible Filter, Sorting, & Groping Data.
ü  Export / Import setting, snapshots of data.
ü  Editing data in the cube via the internet.
ü  Export analysis result to Microsoft Excel.
ü  Easy-to-use UI.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Tuesday, May 3, 2011

Microsoft Office in the CLOUD - Microsoft Office 365.

Microsoft Office 365 for professionals and small businesses is a subscription service that combines Microsoft Office Web Apps with a set of web-enabled tools that are easy to learn and use, that work with your existing hardware, and that come backed by the robust security, reliability, and control you need to run your business.

Two beta programs are being offered for Office 365.
·         A )Office 365 (Plan E3)
Is ideal for larger, IT-based organizations.

·         B) Office 365 (Plan P1)
Is best suited for professionals and small businesses - ideal for organizations that have 25 or fewer employees and no IT support.

New Best thing with office 365 "Office in the cloud".
The Office 365 service offering is that and so much more! With some Office 365 plans, you have access to familiar Office applications that are installed locally on your PC, but are downloaded and licensed from the cloud. Those Office 365 service plans expand this experience by combining those Office applications with the latest versions of our cloud productivity services: Exchange Online, SharePoint Online, and Lync Online. These services combine to provide you with the most comprehensive productivity solution for your business needs.

Components/Products in Microsoft Office 365.
·         Microsoft Exchange Online
Based on the same technology as Exchange Server 2010[citation needed], Microsoft Exchange Online is a service for email and personal information management features such as calendaring, contact lists, and task lists. It also includes features such as voicemail delivery to a person's inbox, anti-spam and anti-virus protection, archiving, Conversation View, and MailTips. It can be accessed using Microsoft Office Outlook on the desktop, Outlook Web App in web browsers, and Outlook Mobile on certain mobile devices. Exchange ActiveSync provides mobile connectivity to Exchange services and mobile email applications.

·         Microsoft SharePoint Online
Microsoft SharePoint Online is a service for collaboration, sharing, and editing of documents using internal and external sites including My Sites, Team Sites, Intranet Sites, and Extranet Sites. It can also be used to create public-facing Web sites.

·         Microsoft Lync Online
Microsoft Lync Online provides communications features including presence information, instant messaging, PC-to-PC audio/video calling and online meetings that can include PC audio, video and web conferencing with application sharing, whiteboards, and other collaboration tools. Lync Online is accessed through the Lync client. Lync Online also supports presence information and click-to-communicate features inside Microsoft Office applications. Currently the Lync components of Office 365 exclude Lync's Enterprise Voice feature set.[citation needed]

·         Office Professional Plus
Microsoft Office Professional Plus in Office 365 provides the same client software as the Office Professional Plus product available through Microsoft Volume Licensing, with the exception that the Office 365 version lacks Business Contact Manager.[citation needed]

Office Professional Plus in Office 365 has month-to-month, per-user licensing. It can be installed by users from Microsoft Online Services or from a server hosted by the subscribing organization. It is activated using the same Microsoft Online Services ID that users employ to sign in to Office 365 and the license is renewed on a 30-day cycle. The advantage of this is that organizations can exactly match the number of licenses they pay for with the number of users who need them. Office Professional Plus in Office 365 also includes Office Web Apps.

·         Office Web Apps
Office Web Apps are browser-based versions of Microsoft Excel, Word, and PowerPoint that enable viewing and lightweight editing of Office documents in Web browsers while preserving the formatting of the original documents.

Why Use Microsoft Office 365
In today’s competitive global market, businesses of all sizes need technology that enables flexibility and that cost-effectively adds value to their organization. Cloud-based services provide a way to meet these objectives. They deliver feature-rich productivity tools to users and help to relieve the burden of managing and maintaining business systems—freeing up IT departments and businesses to focus on initiatives that can deliver true competitive advantage.

System requirements Microsoft Office 365
·        Operating systems
Windows 7
Windows Vista with Service Pack 2
Windows XP with Service Pack 3
Windows XP Home Edition is supported, but it will not support federated identity
Windows XP Media Center Edition is supported, but it will not support federated identity
Mac OS X 10.5 Leopard, 10.6 Snow Leopard

·         System software
Microsoft .NET Framework 3.0 (for Windows XP)
Java client 1.4.2 (for Macintosh OS X)

·         Office clients
Microsoft Office 2010 or Office 2007 Service Pack 2
Office 2008 for Mac and Microsoft Entourage 2008 Web Services Edition
Office 2011 for Mac and Outlook 2011 for Mac
.NET Framework 2.0 or later
Microsoft Lync 2010

·         Client applications
Services Connector

·         Browser software—Microsoft Online Portal
Internet Explorer 7 or later
Mozilla Firefox 3.x
Apple Safari 3.x

·         Browser requirements—Microsoft Outlook Web App
Internet Explorer 7 or later
Firefox 3 or later
Safari 3 or later on Macintosh OS X 10.5
Chrome 3 and later versions
Outlook Web App also has a light version that supports a reduced set of features on almost any browser

Features in Microsoft Office 365
·         Reliability - Microsoft Online Services provides a service level agreement (SLA) and has a 99.9 percent scheduled uptime. Microsoft has multiple datacenters, located all over the world, hosting redundant network architecture.

·         Compliance - Microsoft Office 365 services have been certified as compliant with ISO 27001 standards, completed SAS70 Type I and II audits, and achieved the EU Safe Harbor seal.

·         Compatibility - Microsoft Online Services supports not only Windows 7 and Vista (SP2), but also Windows XP SP3. Even XP Home edition or Media Center edition can be used, although it doesn’t support federated identity. Mac users can also access the Office 365 applications, using OS X 10.5 (Leopard) or 10.6 (Snow Leopard).

·         Up-to-date versions - The services run on Exchange 2010 SP1, SharePoint 2010, and Lync. Client software is Microsoft Lync 2010 for Windows (Communicator for Mac), and the enterprise edition includes the licensing for Office 2010 Professional Plus

·         Single sign-on - Assuming your network is running Server 2008 Active Directory on-premises, you can configure Active Directory Federation Services (ADFS) to achieve single sign-on, so that users can log on to the domain and be automatically authenticated to Office 365.

·         Exchange Online - Users connect to Exchange Online via Outlook 2007 or 2010, with such features as Outlook Anywhere (RPC-over-HTTP) and Cached Exchange Mode. With Outlook 2010, you get all the new Exchange 2010 features, such as conversation view, ignore, MailTips, personal archive, protected voicemail, and voicemail preview.

·         SharePoint Online -With Microsoft Office 2010 applications and SharePoint Online, two or more users can edit the same document at the same time. If you don’t have Office installed, you can use Office Web Apps to work with your documents in a Web browser.Your SharePoint sites work with all Microsoft Office 2010 applications, including Word, Excel, PowerPoint, Access, Outlook, InfoPath, SharePoint Designer, and SharePoint Workspace.

·         Lync Online - Users can connect to Lync Online directly over the Internet (without having to be on the corporate network via VPN or RAS), but file transfer is not available in that situation.

·         Security - Office 365 applications are accessed through 128-bit SSL/TSL encryption so that if a transmission is intercepted by someone without authorization, they won’t be able to read it. Antivirus signatures are kept up to date, and security measures are applied in accordance with the Microsoft Trustworthy Computing initiative.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

Monday, May 2, 2011

To get a comma separated list in SQL & MDX.

To get a comma separated list in SQL as well as in MDX use below queries.

1 -  SQL

Select Name from Sales.SalesReason

Select Name from Sales.SalesReason

                        ',' + s.Name
                        Sales.SalesReason  s
                  ORDER BY
                  FOR XML PATH('')
      ,2,200000 ) AS NAME

2 - MDX

SET [COMMA] AS [Sales Reason].[Sales Reasons].[Sales Reason].MEMBERS
      } ON 0

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2