How can we rollback mistakenly UPDATE statement change in SQL Database.?

Hello all,

Beside than my regular helpful blog posts, it’s just a discussion type blog post that might be useful to all of us.

Let’s say we have a table customer and If someone tries to update a single row within it but by mistakenly update the entire row within a customer table. while update query someone forgot to put where condition and now all the rows of customer table are updated.

Is there any solution to rollback the update statement and retrieve all rows back for that customer table.?

After research ,I have found as the only option is to restore that previously backup Database . If our database runs in full recovery mode, we can:

* backup the transaction log

* restore the database from a full backup

* restore all transaction logs (in sequence) to the time right before the accident occurred.

Also, there is some cool tool called ApexSQL-Log which allows us to rollback the changes. But I haven’t tried it yet before but I have downloaded the product and going for a test. You can easily download from the link below: http://www.apexsql.com/sql_tools_log.aspx

So if anyone knows some idea regarding on this and if there is any solution lately with the release of MS-SQL 2012 then really want to here.

Regards,

Anil Maharjan

SSRS-2012 Reporting Services Configuration integrate in SharePoint 2010.

Hello all,

Once again , time for some blog post and today I just want to share some tips regarding the SSRS 2012 Reporting services configuration integrate in SharePoint 2010.

Recently, I tried to integrate the SSRS 2012 within SharePoint 2010 and configure the reporting services from native mode to SharePoint integrate mode in order to integrate reports from the SSRS server in the SharePoint site.

For this generally, we use the Reporting service configuration manager and within the configuration manager, I cannot set the report server mode to SharePoint since it is default set to native mode and there is no any dropdown box to choose to SharePoint mode and create RS-database.

Finally , after some Research I found that :

It’s not possible anymore with SQL Server 2012 to configure Reporting Services for SharePoint in the Reporting Services Configuration Manager. We have to use the SharePoint Central Administration to Configure Report Server Integration in SharePoint mode.

Check out the link below for the solution for this thread

http://technet.microsoft.com/en-us/library/gg492276

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/358ac527-ac08-4f2c-8d6f-384c35dd9db8

http://technet.microsoft.com/en-us/library/bb326213.aspx

Thanks,

Anil Maharjan

Hadoop Summit 2012- Big Data in Focus !!!

Hello all,

This is not any blog describing about something helpful but just want to inform all the data and technology loving person that Hadoop Summit 2012 is going to be held soon.

Looking forward to hear more about the Hadoop Summit 2012 which is going to be held tomorrow ,JUNE 13th – 14th @ San Jose Convention Center .Wish to watch the live coverage of Hadoop Summit 2012 and hear more tweet’s regarding on this and upcoming focuses of this summit.

For more:

http://hadoopsummit.org/

http://siliconangle.tv/channels/Hadoop

Also, I have only few knowledge regarding on this ‘Microsoft Big Data and Apache Hadoop’ but want to learn more on this .So if anyone knows some good links or something regarding on this technology then please you can share and provide some knowledge or help along within this blog. That would be really appreciated 🙂 ..!!!

Also, really looking forward if any universities offering the Master’s courses that includes Big Data and Apache Hadoop.

Regards,

Anil Maharjan

SSAS Compare, a cool tool for Microsoft Business Intelligence professionals.

Hello all,

It’s been long time gap and I was really missing my blogging time part, busy blogging for my friends at Two Hour Blogger.

Through this blog I would just want to share the new tool developed by the Red –Gate called

SSAS Compare . which I think , SSAS Compare is one of the cool tool .

It has a great feature as

With SSAS Compare, you can select SSAS databases to compare differences between cubes, dimensions, measures and other objects.

SSAS Compare can create an XMLA deployment script to deploy changes to the target database.

This tool can be helpful when we have several versions of the same catalog/database within Production side and development side and needs to compare and find out how they differ. sometime we get confuse that which one is the latest catalog and what’s the latest different within the catalog between Production side and development side.

So, this tool can be must helpful which allows to compare the catalog and choose the required cube structure need to deploy by simply generating the XMLA script.

Help on installing the SQL Server 2012 integrated along with SharePoint 2010.

Hello all,

After long time gap, I just want to post a blog that might be helpful to the one who is going to learn, try and setup these great products of Microsoft as SQL Server 2012 and SharePoint 2010.

If you are going to learn, try and install SQL Server 2012 integrated along with SharePoint 2010 then this blog might be very helpful.

Some of the things you should be consider before installing and some steps for proper installation are as:

Please read the requirements of your machine and requirement needed for setup clear fully before you began to install.

Step 1. Firstly you need to install the Microsoft SQL Server 2012 either standard or enterprise version .The installation setup can be easily found in the link below for 180 days as Evaluation version.

http://www.microsoft.com/sqlserver/en/us/default.aspx

For installing the SQL Server 2012 properly you can see the nice video in

http://www.youtube.com/watch?v=-6D69P6O9AI

Step 2. After successful installation of the SQL Server 2012, you can install SharePoint 2010 either in Standalone mode or in Server Farm mode. Here is the link to download

http://technet.microsoft.com/en-us/evalcenter/ee388573.aspx

Here, The Standalone SharePoint installation is supported and common for development environments of Reporting Services.

However a SharePoint farm installation is recommended for a production environment. A farm is enabled when you choose the Server Farm option in SharePoint setup. You must have a farm installation if you want to use PowerPivot for SharePoint or Power View.

Note: You need to install the SharePoint 2010 Enterprise Edition for Server Farm option or to use PowerPivot for SharePoint. We can’t simple install the Server Farm mode within Windows7 OS .If you want to try then you should have to create the Virtual Machine’s for Windows Server 2008 within Windows 7 OS. Also, the user must be the domainuser, and we can’t use the local user.

Step 3. If you are trying to install the SharePoint 2010 in Standalone mode for development purpose then the link below shows the Step by step guide to install SharePoint 2010 in Standalone mode.

http://jeanpaulva.com/2012/02/22/sharepoint-2010-installation/

After installation of SharePoint 2010 as per above, while configuring SharePoint 2010 central admin (SharePoint product configuration wizard) after configuring the IIS 7 as needed you will get the errors as.

i. Could not load file or assembly ‘Microsoft.IdentityModel, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies.

Simple download and install the file from link below

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=17331

ii. An exception of type Microsoft.SharePoint.Upgrade.SPUpgradeException was thrown. Could not load file or assembly ‘System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies.

Simple download and install the file from link below

http://go.microsoft.com/fwlink/?LinkID=122517

After this you will successfully install and configure the SharePoint 2010 in Standalone mode. If required you can update the Hotfix for SharePoint 2010 SP1 from Microsoft too.

Step 4. Basically you just need to be careful with Reporting Services configuration, you need to choose SharePoint Integrated Mode in report server mode option while configuration and create the report server DB for SharePoint integrate mode, if you plan to embed reports within your SharePoint.

If you have chosen the Reporting Services Add-In for SharePoint Products while installing then you don’t need to install again Add-in.

If you are configuring a report server to run within a deployment of a SharePoint product, you must specify integration settings that determine the connection between the SharePoint technology instance and a Reporting Services report server

Here is the link to Configure Report Server Integration in SharePoint Central Administration

http://msdn.microsoft.com/en-us/library/bb326213.aspx

Step 5. Also, if you plan to use Power Pivot for SharePoint then you need to run install SQL Server 2012 again and choose for SQL Server Power Pivot for SharePoint mode which requires the Domainuser for analysis services to install. Also, for this you need to install the SharePoint 2010 in Server Farm Mode and also needs to configure as similar .Here is the link to setup in Server Farm mode.

http://msdn.microsoft.com/en-us/library/hh231680.aspx#bkmk_sp1

Also, to learn PowerPivot more here’s the link below and also you need Ms-Office 2010.

http://www.microsoft.com/en-us/bi/powerpivot.aspx

Besides these you also need to download the Visual Studio 2010 from link below

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=28992

References:

http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/ab8046fb-0cf4-4891-9165-02ca1ec91e31

Hope this Blog post will surely helpful to the one who wants to learn, try and setup these great products of MicrosoftSQL Server 2012 and SharePoint 2010.

Regards,

Anil Maharjan

Showing Measure values as either ‘0’ or ‘NULL’ in cube ?

Hello all,

It been long time ,after a long time gap I would love to post this blog which might be helpful to the one who works in SSAS.

While designing a cube everyone simply doesn’t care about the ‘NULL’ or ‘0’ values within the cube output. So through this blog , I want to share with you guys that how can we show the NULL or 0 value output within the cube.

Also , if anyone need to show the exact client data either it’s NULL or as 0 within a Cube then for them this blog might be helpful too.

One can handle/Preserve the NULL values of data by following the blog by DevinKnight which is one of nice blog I referred

http://bidn.com/blogs/DevinKnight/ssis/1587/ssas-preserve-measure-null-values

Since , BIDS will treat all the NULL values as ‘0’ while calculation and if you have hundreds of calculated measure and you want to show the values as either NULL or 0 as per you need.

And there you need to handle the 4cases mostly NULL/NULL,NULL/0 , 0/0, 0/NULL.

If divisor is zero, then there will be no error, the cell value simply will be positive or negative infinity represented as 1.#INF – but people don’t like this for some reason and prefer to replace it with NULL, which makes the user wonder why there is no data for that calculated Measure.

Let’s say, we have a calculated measure as [Measure].[Cal] and you need to show like this.

[Measure].[Cal] =[Measure].[A]/ [Measure].[B]

case 1 when [Measure].[A]=0 and [Measure].[B]=0, then show 0,

case 2 when [Measure].[A]= NULL and [Measure].[B]=NULL then show NULL,

Case 3 when [Measure].[A]= 0 and  [Measure].[B]=NULL then show 0,viceversa

Case4 when  [Measure].[A]= value1 and  [Measure].[B]=value2 then show value1/value2,

The best approach to calculating [Measures].[Cal] is the following:

 IIF([Measures].[B] = 0, null, [Measures].[A] / [Measures].[B] )

If you return null, NON EMPTY will work and the calculation will be faster as there are optimizations in the engine to handle this specific pattern.

Least one used:

 IIF([Measures].[B] = 0, 0, [Measures].[A] / [Measures].[B] )

You don’t want to return a 0 in the second argument of the IIF as this will always return a value and will mean that you cannot use NON EMPTY in your queries (as the calculation would never return an empty value).

The above all 4 case can be handle within one nested IIF condition as you only want to display NULL if both A and B are NULL, otherwise it is always 0 or the number.:

IIF(ISEMPTY(A) AND ISEMPTY(B), NULL, IIF(A=0 OR B = 0, 0, A/B)) i.e

IIF(     

  ISEMPTY([MEASURES].[A]) AND ISEMPTY([MEASURES].[B]), NULL,

		IIF([MEASURES].[A] = 0 OR [MEASURES].[B]=0 , 0, [MEASURES].[A]/[MEASURES].[B])

   )
Some more details to handle like this are further describe within Mosha Blog.
http://sqlblog.com/blogs/mosha/archive/2005/06/30/how-to-check-if-cell-is-empty-in-mdx.aspx

Hope this post will be helpful for all of us in the field of BI mainly in SSAS/MDX and one who is looking for such solution.

Regards,

Anil Maharjan

Download a free copy of Microsoft Press eBook ‘Introducing Microsoft® SQL Server 2012’

Hello all,

After a long time gap, I just want to share a link to download a free copy of Microsoft Press eBook ‘Introducing Microsoft® SQL Server 2012’.

This could be a great eBook for you to learn and know about what’s inside the ‘ SQL Server 2012 ’ released by Microsoft.

I have already downloaded and have started to read and learn the things .Hope you will too.

This link could be helpful for all of us in the field of BI-SQL server to know more about SQL Server 2012 , virtual lunch event-sessions and more.

http://www.microsoft.com/sqlserver/en/us/default.aspx

Contents at a Glance

Part I Database Administration (by Ross Mistry)

1. SQL Server 2012 Editions and Engine Enhancements
2. High-Availability and Disaster-Recovery Enhancements
3. Performance and Scalability
4. Security Enhancements
5. Programmability and Beyond-Relational Enhancements

Part II Business Intelligence Development (by Stacia Misner)

6. Integration Services
7. Data Quality Services
8. Master Data Services
9. Analysis Services and PowerPivot
10. Reporting Services

Regards,

Anil Maharjan

How can we find the distinct count of all the Hierarchy and attribute members from all the dimensions.?

How can we find the distinct count of all the Hierarchy and attribute members from all the dimensions.?.

How can we find the distinct count of all the Hierarchy and attribute members from all the dimensions.?

Hello all,

Firstly ,Happy New Year 2012 🙂 to all .!!!

In order to start this year blogging, I would like to post another blog which might be helpful to the one who works in SSAS.

Last time, I was just trying for some trick or some queries in order to quick find the distinct count of all the Hierarchy and attribute members from all the dimensions .

For Example, If we have ‘ PaidDate ‘ dim having Hierarchy members as PaidYear ,PaidMonth, PaidDay then we need to calculate the Distinct count of all those members and similarly for all the others dimension .Then I simply used the

SQL query method as:

Select count(Distinct(PaidYear,PaidMonth,PaidDay)) from PaidDate and similar for others but all I want is some Quick methods or some DMV queries to find out my solution.

After some research and through MDSN, I finally got my solution .It’s just a simple DMV query as

select * from SYSTEMRESTRICTSCHEMA($SYSTEM.DISCOVER_DIMENSION_STAT,DIMENSION_NAME='Dimension', DATABASE_NAME = 'Database')

--Dimension -replace with the Dimension you want to find Distinct count and Database -replace with your DB catalog.

In the returned tabular result, the ATTRIBUTE_COUNT column is to describe the count that I was looking for.

Is there any alternative solution for this kind of issue .Hope someone have some idea regarding on this.

Hope this post will be useful for all of us in the field of BI mainly SSAS.

Regards,

Anil Maharjan

How Can I know who has changed anything in SQL?

Hello all,

After such a long time , I would like to post some another blog which might be helpful to the one who works in SQL.

I need to find the one who has changed the tables within my SQL Database that I have created.

For certain reason ,we may need to find /to know who has done any changes within SQL. Such as add view, delete table, modify table, create database, add column, stopped SQL agent job, create new job, modify rules, add roles, new login, and etc.

In order to trace this information and find out my solution, I have researched somewhat and find the solution as.

we can enable and disable the default trace in SQL Server. Meanwhile we can use the default trace enabled option to enable or disable the default trace log files.

If we want to capture the information about who changed the objects in SQL Server. Please try to run the script, which works for me as below:

DECLARE @filename VARCHAR(255) 
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('', REVERSE(path))+1) + 'Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

--Check who dropped and created objects, altered objects
SELECT gt.HostName, 
       gt.ApplicationName, 
       gt.NTUserName, 
       gt.NTDomainName, 
       gt.LoginName, 
       gt.SPID, 
       gt.EventClass, 
       gt.IntegerData, 
       te.Name AS EventName,
       gt.EventSubClass,      
       gt.TEXTData, 
       gt.StartTime, 
       gt.EndTime, 
       gt.ObjectName, 
       gt.DatabaseName, 
       gt.FileName, 
	gt.IsSystem
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
where gt.ObjectName ='DimTable' and gt.DatabaseName ='Database'
ORDER BY StartTime;
 --DimTable -replace with the trace table and Database -replace with your DB

I am able to find the one who have changed my tables that I have created.

Also we should have to know that, SQL Server only shows you the IP address of the Terminal Server machine. It has no knowledge about how the user connected to that server. You would have to find the RDP logs and enter them into the database somehow to correlate the information. Which still can be inconclusive if there are more than one user connected through RDP at the same time.

Reference from the link:

http://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server—the-power-of-performance-and-security-auditing/

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/ef5f82e3-71a6-4e65-989f-5ab7b84bbc82

Is there any alternative solution for this kind of issue .Hope someone have some idea regarding on this.

Hope this post will be useful for all of us in the field of BI mainly SQL.

Regards,

Anil Maharjan