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 to drop/delete all the user define tables within a SQL database.

Hello all,

Firstly on this lovely Valentine’s day esp, I wish Happy Valentine’s day to all the loved ones 🙂 .

I would like to post some another blog which might be helpful to the one who works in SQL.

Sometimes we might need to delete all the user define tables from a Database in SQL. So, If any one just need to delete all the user define tables from a SQL DB then you can use just a simple Query as below:

USE Database:--replace with your Database name.
GO
DECLARE @tbl_name as varchar(256)
DECLARE @flag as int
DECLARE @name as varchar(256)
set @flag=(SELECT count(*) FROM sysobjects where type='U')
while(@flag>0)
BEGIN
set @name=(SELECT top 1 name FROM  sysobjects where type='U')
set @tbl_name = 'drop table ' + @name
exec(@tbl_name)
print @tbl_name
set @flag=@flag-1
end

within a database we can have objects as TABLES, VIEWS, FUNCTIONS, PROCEDURES, TRIGGERS, etc. are there. So, If anyone just want to delete only User Define TABLES without dropping the entire Database and other objects the above query is the best one. If someone drops the database that means those objects needs to be recreated. So,in order to save the time and for quick approach I found this useful .

This is just a simple example and looks simple but it might help for the one who indeed needs such a solution.

Regards,

Anil Maharjan

How to delete a particular row from a table 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.

Sometimes we might need to delete the particular row from a table in SQL Database.

While working someone might mistakenly insert the duplicate same data multiple times within a table that might contains a huge amount of data and after populating that table .He/she wants to delete that duplicate data inserted from a table.

So, for this we might drop and recreate the table or truncate the table and insert again. But, if it is a large table then it would be better to find the duplicate row within a table and delete duplicate one which helps in time consumption and server utilization too.

In order to obtain this , we can simple used a simple query as

WITH newtbl as(SELECT *, 'row number'=ROW_NUMBER() over (order by FirstName) FROM CustomerList)
DELETE FROM newtbl WHERE [row number]=2

--where CustomerList is a user table having dublicate record.

This is the tested script for Test Database where CustomerList table have duplicate records which needs to be delete.

This is just a simple example and looks simple but it might help for the one who indeed needs such a solution.

Regards,

Anil Maharjan

How to move the tables without having clustered index from one file group to another file group simply in SQL 2008.?

How to move the tables without having clustered index from one file group to another file group simply in SQL 2008.?.

How to move the tables without having clustered index from one file group to another file group simply in SQL 2008.?

Hello All,

This might not be the proper for the blog post but it helps me to understand more regarding the transfer of tables within different file groups in MS-SQ L. So, I think it will be more helpful to the others who indeed needs such information related to file groups .

I had posted as below within MSDN

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d35ab8ff-a6a1-458c-b3e4-b2c9f6a1ff83

I have few issues regarding the transfer of the tables from one file group to another file group in SQL 2008 and also How can we backup and restore the particular database based on file group level.

Let’s say I have a tables stored within the different FG. such as

Tables File group

Dimension tables Primary

Fact tables FG1

FG2…

zzz_tables DEFAULT_FG

dim.table1 DEFAULT_FG

dim.table2 DEFAULT_FG

… ….

Here all I want to transfer the dim.table1 ,dim.table2 from DEFAULT_FG to the Primary File group .So is there simple methods for transfer the dim.table1,2 from one FG to another .I have tried somewhat but I couldn’t get the exact way .So if someone have better idea please share your knowledge that would be really appreciated.

Secondly after moving those dim.table1 ,dim.table2 from DEFAULT_FG to Primary ,All I want to backup and restore the database only containing the Primary and FG1,FG2… not a DEFAULT_FG.Is it possible or not.?

Currently I have no any clustered index defined within the dim.table1 ,dim.table2 and so on.

Cannot we transfer the tables from DEFAULT_FG to another FG without having clustered index on that tables or without select/insert operation so that I couldn’t have to recreate the tables. I have researched somewhat and found that we couldn’t move the tables without having the clustered index to another file group simply in MS-SQL so sad :(..!!!

Hope to hear from the one who knows better approach for this kind of task .Your simple help will be much appreciated.

Some helpful links regarding transfer of tables between different FG’s and restore/backup FG’s.

http://www.sqlservercentral.com/articles/Administration/64275/

http://www.sqlservercentral.com/Forums/Topic695367-146-1.aspx#bm695717

http://sqlserverpedia.com/wiki/Restoring_File/Filegroup_Backups

Well after all my full day research on this topic had paid off, I finally got the solution and am so happy to research on these things. It makes us feel really happy after all our research and hard work doesn’t goes as waste.

Finally I got what I am looking for and want to make sure that I am able to transfer the tables from DEFAULT_FG to another FG without tables having clustered index on that tables .

With the help of the link below I finally got my solution where Roberto’s coded store procedure simply works for this.

Really thanks to him for his great post and thanks to everyone for their response in MSDN and their valuable time.

http://gallery.technet.microsoft.com/scriptcenter/c1da9334-2885-468c-a374-775da60f256f

Regards,

Anil Maharjan