OLAP PivotTable Extensions

Wow, I am so excited that there is such a cool extension available for the excel 2007 onwards ,firstly I don’t know that we can use this cool features add-in extension within an excel .I have often used the PivotTable but I never try it for this particular extension and its features.

I had bookmark for this particular page link a few months ago while I was doing my intern program but suddenly I check it back again now and I found why should not I had look the page clear fully before and why I had not use it or try it out before asking myself. 

But , nice to know that it has some great features like :
1. Private Calculated Members,
2. Calculations Library,
3. View PivotTable MDX,
4. Changing PivotTable Defaults,
5. Searching,
6. Distributing PivotTables

Among these I found the most useful and cool features is viewing entire MDx script and searching features.

Also as we know there are a lot of different tools/software’s that provides these facilities but one simple thing is that we can easily download this extension and it is free too.
You can visit the links as
http://olappivottableextend.codeplex.com/
http://www.ssas-info.com/analysis-services-tools/298-olap-pivottable-extensions-

Thanks,
Anil Maharjan

How to schedule and automate backups of all SQL Server databases in SQL Server.

Hello all,

With the course of time, you might need to backup all the databases within a server instance automatically and schedule to backup these database monthly wise or according to your specific time set.

Also, what I want to say is that ,I cannot stop myself to write this another blog of mine.

writing a blog and sharing some knowledge through the blog makes us really feels happy and also somewhat we also become addicted to it. 🙂

This may be simply for one but for someone it will be helpful for those who don’t know how to do it.

May be there are a lot of alternative methods to perform this task and many solution’s.

But I have researched a lot regarding to backup all the SQL database as well the SSAS catalogs database backup with in a particular server instance.

Here ,I am going to show how to obtain the SQL database backup automatically along with schedule time set.
Firstly, I have created a SSIS package where I used simply “Back up Database Task “ from toolbox and click right then clicking in edit tab, we can set the connection as show in figure1 below:

Then we can set the Backup Type as full or other type after that you can select the databases to backup as show in figure2 below where we can set required databases to backup.

After that you can set the other different properties like
1.Backup set Expire
2.Bakup to
3.Append or overwrite if backup exist
4.for everyday backup options
5.Backup location directory currently I set to default location.
6.allow compression or not.
You can figure out by looking the figure3 below.

Now all you need to create a job agent to schedule the backup task and run SSIS package. In order to do this you can check out the link by Brian Knight as
http://www.sqlshare.com/running-and-scheduling-ssis-packages_53.aspx

or simply go to SSMS and start Sql Server Agent and right click and create new job where you have to setup some properties as shown in figure4.

Here you can schedule the backup going into the schedule tab and then just check the job working or not by right clicking it and Start job at step then you can check the all the backup database within the default location as : C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBackup

Hope you get some ideas to obtain the schedule Sql databases backup automatically ,also I am thinking to post into my another blog for “ Automate and schedule all SSAS catalogs database backup with in a server instance by using Job Agent” or similar approach which have taken most of my research time and I am happy to obtain the result successfully.

Lastly,this may be simple but I think it may work out for someone .

Thanks,
Anil Maharjan

Backup and Restore Cube at a time using SSIS Package.

Hello all,

Firstly, what I want to say is that ,I cannot stop myself to write this another blog of mine.

writing a blog and sharing some knowledge through the blog makes us really feels happy and also somewhat we also become addicted to it. 🙂

Also, this may be simply for one but for someone it will be helpful for those who don’t know how to do it.

May be there are a lot of method to perform this task and many solution’s.

While working I have to take a Backup of Cube from one database and Restore it into another database within a same machine.So,for this i usually do manually by right clicking the Backup and Restore it to another database but every time say development cube changes frequently and i have to take backup and then again restore it into production cube.

Doing manually seems little lazy,so i think of doing it dynamically or say by just a click so that.

For this I use a SSIS package where we can use the “Analysis Services Execute DDL Task ” from toolbox and by editing it, we can set/configure the values as:

Also, i forgot to mention that for the restore and backup XMLA script ,we have to right click on the database and click restore/backup and then click the script button so that it will generate the XMLA script for restore and backup to particular database.After generating the XMLA,we can directly put into the “Analysis Services Execute DDL Task” by configuring the “SourceType” :Direct input and SourceDirect : XMLA script (i.e of backup / Restore).

But here i use the file connection for pointing the XMLA script from particular location so that we can execute this for different database by only changing the XMLA file.

This may be simple but i think it may work out for someone .

Thanks,

Anil Maharjan

SQL Server Reporting Services – Displaying some message within a Tablix if there is no data.

Hello all,

This is my first article that I am trying to write .I am encourage to write this article after joining a lot of BI related network and reading a lot of articles/blogs regarding different topic. so, this may be simply for one but for someone it will be helpful for those who don’t know how to do it ..

while working in .rdl design using BIDS , I need to display certain message within a tablix toolbox, if the table doesn’t contain’s any data to display or there is no row count. In normal it will display the data if there is certain to display but if there is not any data to display then we have to display the message as like i.e “Data doesnot match”

For this we need to add a new row within a same table and set the properties of ‘Hidden’ field as =IIF(Count(“DSName”)>0,true,false)

Then within a same row we need to add a ‘Placeholder’ by right clicking within a row and set its properties value expression as

=IIF(count(Fields!Row1.Value,”DSName”)>0,””,”No data qualifies for this section”)

Also in another terms,

Remove no rows property for the table,added another table header row with the expression

=IIF(count(Fields!Row1.Value,”DSName”)>0,””,”No data qualifies for this section”)

and set visibility hidden expression for the added table header row as

=IIF(Count(“DSName”)>0,true,false)

Replace”DSName” with your datasetName.

Hope this will help for someone .. 🙂 !!!..

You can see some snapshot as:

You can see output as if there is no data to display or no row count:

Thanks,

Anil Maharjan