May 18 2012

Periodic Table of the HTML5 Elements

Category: HTML5 — abhishek.shukla @ 10:32 | |

Tags:

May 18 2012

Ultimate HTML5 Cheatsheat [Infographic]

Category: — abhishek.shukla @ 10:11 | |

;Location=http://www.testking.com/techking/infographics/ultimate-html5-cheatsheat/]

 

 

Tags:

May 18 2012

Ultimate HTML5 Cheatsheat [Infographic]

Category: — abhishek.shukla @ 10:11 | |

;Location=http://www.testking.com/techking/infographics/ultimate-html5-cheatsheat/]

 

 

Tags:

Feb 24 2012

assAS

Category: — abhishek.shukla @ 09:24 | |

<script src="seadragon-min/seadragon-min.js" type="text/javascript"></script>

Tags:

Feb 2 2012

Consolidated Download list for sql server

Category: — abhishek.shukla @ 17:47 | |

http://www.softpedia.com/get/Internet/Servers/Database-Utils/Microsoft-SQL-Server.shtml

Tags:

Jan 29 2012

dfdsfsdfs

Category: — abhishek.shukla @ 15:07 | |

Tags:

Jan 28 2012

Best practice for SSRS deployment

Category: SSRS 2008 — abhishek.shukla @ 23:58 | |

While SQL Server Reporting Services (SSRS) platform is not hard to learn and work with, it is still a honestly complex equipment. Successful utilization of SSRS requires a combination of database, administration, report building and data analysis skills. Such a combination of expertise is regularly hard to place together, especially in smaller companies where one person might wear many hats.

As a consultant, I have seen several SQL Server Reporting Services deployments that could have benefited from a few simple SSRS best practices. Here are a few.

Back up the key.

SSRS uses encryption to protect insightful data in its configuration. Things like connection strings and passwords are stored in the back-end ReportServer database and in the configuration files. They are encrypted using an encryption key that’s stored in SSRS. If you go SSRS to another server, you need to use the same encryption key to decrypt all encrypted data. Therefore, proper encryption key management is extremely vital.

When you install SSRS, the first thing you should do is use the and back up the encryption key to a password-protected file. Keep a copy of this key file on the SSRS server and also in a safe spot somewhere on the network. If you ever need to migrate SSRS to another server, you can use the same configuration manager to restore the key from the original server. Otherwise, you will have to manually re-make all your data sources and other encrypted content. That’s not something you want to do, especially if your SSRS server is not functional and you are promptly trying to bring up SSRS on another server. Even though Microsoft has emphasized the importance of keeping a backup of the encryption key, I still sometimes find myself at a and learn that the key isn’t backed up.

Use Windows Active Directory groups to control security.

Systems administrators have long been following the practice of making Windows groups and granting privileges to the group as a replacement for of assigning privileges to party user financial statement. This practice makes a lot of significance, since you can easily add or remove users from a group and make your security management much simpler. But I don’t see this practice as widely used among developers and database administrators. I’ve seen many SSRS installations where whoever was managing privileges assigned individuals access to reports or report folders as a replacement for of making groups like Marketing or Management to simplify administration.

Use report folders to control security.

Just as it makes significance to use Windows groups as a replacement for of user financial statement, you’ll gain a similar advantage by managing security at the folder level. Group your reports into logical groups, house them in a report folder and then assign privileges to the folder rather than to party reports. SSRS also allows you to cascade privileges to the subfolders so you can design a hierarchy of privileges in which higher privilege groups can view all folders, while other groups can view only reports closer to the root folder.

Use saved authentication when configuring report data sources.

While using Windows Authentication is regularly the most recommended option, it doesn’t always work well in SSRS. If you configure a report to use Windows Authentication to connect to a SQL Server database, it only works if the database is on the same server as the SSRS server. But if you need to connect to another , a “double-hop” authentication is needed — one hop between the browser and SSRS and the other hop between SSRS and the . I had to troubleshoot this issue when a report was working while the user was using a browser on the SSRS server but stopped working when SSRS was accessed from another machine, resulting in double-hop authentication. Theoretically, double-hop authentication should work if you properly configure the  Kerberos on the network, but I haven’t seen much success in that area. You are better off configuring a data source to use a SQL Authentication login, or state a Windows account that should be used to connect to SQL Server.

Back up the SSRS back-end databases.

SSRS uses ReportServer and ReportServerTempDB databases, and you should back those up to a location other than the SQL Server machine they run on. You will need them if your server dies and you need to re-make the SSRS environment; otherwise you will have to redeploy all your reports and redo all configurations. I’ve seen companies building backups to a community drive, but if you lose the whole machine, those will do you no excellent.

Practice SSRS migration to another server.

Migrating SSRS to another server is relatively simple: Back up the ReportServer and ReportServerTempDB databases and the encryption keys. Next, restore them on another SQL Server and configure the new SSRS server to use them. Once you restore the encryption key, your new SSRS environment should be identical. This is a excellent exercise, because if your SSRS server ever dies, you will be able to bring a new server online much quicker.

Keep all reports under source control.

Very regularly, a companionship has several people developing reports and deploying them to the server without having a central location to store the files and keep them versioned. Developers are used to working with such as SourceSafe or SVN, but business users are not used to them. Since they regularly build and deploy reports, they should use the same procedure and discipline to check new reports into a source control and check them out if they need to make modifications. Aside from having your reports in a central house, where they are versioned and backed up, you’ll find it much simpler to build a new SSRS environment, pulling the reports from source control as different to collecting the report definition files from the individuals who urban each report.

While the SSRS best practices in this article are intuitive and simple to apply, not every companionship has them in house. I highly recommend that you check your SSRS configuration and make the recommended configurations. In addendum, remember to back up the keys, the databases and practice migrating to another server. After all that work, your SSRS administration will require less time, and you will be better prepared to deal with an unexpected migration to a new SSRS server.

Tags:

Jan 28 2012

The Power of Reporting Services – Caching

Category: SSRS 2008 — abhishek.shukla @ 22:46 | |

If you haven’t heard the phrase “This report is really slow!” there are only a couple of possibilities: you don’t write or administer Reporting Services reports and are at the wrong blog, or you need to come teach me all you know!

There are many reasons a report can generate slowly. The code could be written poorly, the database could be experiencing high volume, or it could be a huge report that returns a lot of data. There are several strategies to make the report faster. You could rewrite and rewrite, tuning it like an OLTP query. You could throw more and more hardware at the server, or add bandwidth to your network. You could review the report with the business users, and try to determine if the scope could be narrowed down to return fewer results.

There’s another option to consider as well – caching a temporary copy of the report.

Caching will store a temporary copy of the report for a specified period of time. If a cache is a hidden store of objects, where is this hidden? You would find it in your ReportServerTempDB database. When a copy is stored, information is written to the dbo.ExecutionCache table.

Setting up the Cache

Pirates have to hide their treasure, so you too must sneak into Reporting Services under the cover of darkness and set up your cache.

To find it, open your report. Go to the Properties tab, and click Execution.

By default, your report will be set to “Always run this report with the most recent data”. (I’ll cover “Render this report from a report execution snapshot” in a separate post.) Here, you have three choices:

  • Do not cache temporary copies of this report – Every time a user runs the report, it will be generated. It’s like a pirate who never buries the treasure chest, but just leaves it sitting on the beach.
  • Cache a temporary copy of the report. Expire copy of report after a number of minutes – When a user runs the report with a specific set of parameters, the report is generated, and then stored in the cache for a specific number of minutes. This is a straight setting. If the report is run at 9:28 AM, and the setting is 240 minutes, it will expire at 12:28 PM the same day. Think of it as burying the treasure under three inches of sand, knowing the wind will blow away an inch an hour.
  • Cache a temporary copy of the report. Expire copy of report on the following schedule – this option allows you to have the report copy expire in cache on a day and time of your choosing. With this option, it’s like the pirate buried a timer, and the chest only pops up when he wants it to.

Does It Really Work?

Or have I been drinking the rum the pirate buried with the treasure?

I took a report from my production environment and tested it. The report was written as:

SELECT *
FROM ViewName

This view runs on the largest table in this database, and aggregates the data. (I didn’t write it. But I will be re-writing it.)

First, I ran the query in Management Studio. It returned 2 rows in 2 minutes, 53 seconds.

Then, I ran the report from Reporting Services. It executed in 2 minutes, 44 seconds.

Then, I enabled caching and set it to expire in 30 minutes. I ran it again one minute later, and it executed in less than one second.

Yes, less than one second. It just popped up.

Go ahead – find your nastiest, ugliest, longest-running report. Set up caching. Time the execution before and after. I’d love it if you posted your results in the comments!

How to Make This Even More Awesome

There is one small dilemma here: the first person to run the report after it expires from the cache will be faced with the nasty, ugly, long-running report. Wouldn’t it be great if there was a way to expire and build the cache on a regular basis, so it was that fast every time?

It can be done! You need to set up a data-driven subscription, using the null delivery provider. The best part: I already wrote about that! http://jesborland.wordpress.com/2010/09/03/the-power-of-reporting-services-subscriptions-–-data-driven-subscriptions-–-null-delivery-provider/

Some Important Notes

A report won’t stay in the cache indefinitely. It will be removed if the report is set to expire, if the report is modified, if the parameters are modified, if the credentials of the data source change, if any other execution options change, or if you delete the report.

You can’t set up caching for a report that uses Windows authentication or one that prompts the user for credentials.

If your report has a parameter, every different parameter option that the report is run with will store another copy in the cache. Example: my report has a parameter of “Folder”. I have ten options – one for each department (Accounting, Marketing, etc.). If the report is run with “Accounting” selected, that will produce one copy in cache. If it is run two minutes later with “Marketing” selected, another copy will be stored in cache. Thus, if you have a report with a parameter that has a lot of options, and different options are frequently selected (for example, a customer drop-down with one hundred options), caching may not be an ideal solution for that report.

Tags:

Jan 28 2012

The Power of Reporting Services Subscriptions

Category: SSRS 2008 — abhishek.shukla @ 22:03 | |
The Power of Reporting Services Subscriptions

Tags:

Jan 28 2012

Types of Reports in SSRS

Category: SSRS 2008 — abhishek.shukla @ 22:00 | |

In Reporting Services, you can use reports in a variety of ways. This topic describes the terminology used to describe the various types of reports and the ways reports get created and used. A single report can have characteristics from more than one type; for example, snapshot reports can be parameterized, ad hoc reports incorporate clickthrough report functionality due to the report models upon which they are based, and subreports can be linked reports.

With Reporting Services, you can create the following types of reports:

There are several ways to think about report type. You might think about it as the way data appears in the report. In Reporting Services, the appearance of data in a report depends on the type of data region you use; for example, tabular reports and chart reports use different data regions. For more information about how to display data, see Data Regions and Maps (Report Builder 3.0 and SSRS). Likewise, the functionality that is available in a report depends on the output format; for example, interactive features like drillthrough reports are available in Web-based export formats but not in all Image-based export formats. A report's final output format affects which features you can include in a report. For more information about design considerations for various export formats, see Exporting Reports (Report Builder 3.0 and SSRS).

There is also terminology associated with the stage of processing a report is in. For more information about the differences between reportdefinitions, publishedreports, and renderedreports, see Reports, Report Parts, and Report Definitions (Report Builder 3.0 and SSRS). Finally, for information about report scheduling and on-demand reports, see Scheduling Reports, Shared Datasets, and Subscriptions.

A parameterized report uses input values to complete report or data processing. With a parameterized report, you can vary the output of a report based on values that are set when the report runs. Parameterized reports are frequently used for drillthrough reports, linked reports, and subreports, connecting and filtering reports with related data.

Using Parameters

Parameters are used in dataset queries to select report data, to filter the result set that the query returns, or to set layout properties used to display or hide parts of a report. You can also specify cascading parameters that populate a series of dependent, drop-down parameter lists. For example, a drop-down list of Region parameter values can be used to populate a drop-down list of City parameter values.

You can use parameters with linked reports by pairing a specific parameter with each linked report to change the outcome. For example, you can create a single regional sales report that shows the sales for all regions, and then use a parameter for each linked report to filter data for a particular region. Specific parameter values can be stored with the report so that users do not have to type values.

Not all parameters may be visible in the report at run time. A report author, report server administrator, or content manager can specify which values to use and then hide the input fields on the report.

Query Parameters and Report Parameters

Reporting Services supports two kinds of parameters: query parameters and report parameters. Query parameters are used during data processing to select or filter data. Query parameters are specified in the syntax of a data processing extension. If a query parameter is specified, a value must be provided either by the user or by default properties to complete the SELECT statement or stored procedure that retrieves data for a report. Report parameters are used during report processing to show a different aspect of the data. A report parameter is usually used to filter a large set of records, but it can have other uses depending on the queries and expressions used in the report. Report parameters differ from query parameters in that they are defined in a report and processed by the report server, while query parameters are defined as part of the dataset query and processed on the database server. For more information, see Parameters (Report Builder 3.0 and SSRS) and Setting Parameter Properties for a Published Report.

A linked report is a report server item that provides an access point to an existing report. Conceptually, it is similar to a program shortcut that you use to run a program or open a file.

A linked report is derived from an existing report and retains the original's report definition. A linked report always inherits report layout and data source properties of the original report. All other properties and settings can be different from those of the original report, including security, parameters, location, subscriptions, and schedules.

You can create a linked report on the report server when you want to create additional versions of an existing report. For example, you could use a single regional sales report to create region-specific reports for all of your sales territories.

Although linked reports are typically based on parameterized reports, a parameterized report is not required. You can create linked reports whenever you want to deploy an existing report with different settings. For more information, see Adding, Modifying, and Deleting Linked Reports.

A report snapshot is a report that contains layout information and query results that were retrieved at a specific point in time. Unlike on-demand reports, which get up-to-date query results when you select the report, report snapshots are processed on a schedule and then saved to a report server. When you select a report snapshot for viewing, the report server retrieves the stored report from the report server database and shows the data and layout that were current for the report at the time the snapshot was created.

Report snapshots are not saved in a particular rendering format. Instead, report snapshots are rendered in a final viewing format (such as HTML) only when a user or an application requests it. Deferred rendering makes a snapshot portable. The report can be rendered in the correct format for the requesting device or Web browser.

Report snapshots serve three purposes:

  • Report history. By creating a series of report snapshots, you can build a history of a report that shows how data changes over time.

  • Consistency. Use report snapshots when you want to provide consistent results for multiple users who must work with identical sets of data. With volatile data, an on-demand report can produce different results from one minute to the next. A report snapshot, by contrast, allows you to make valid comparisons against other reports or analytical tools that contain data from the same point in time.

  • Performance. By scheduling large reports to run during off-peak hours, you can reduce processing impact on the report server during core business hours.

For more information about creating report snapshots, see Setting Report Processing Properties.

A cached report is a saved copy of a processed report. Cached reports are used to improve performance by reducing the number of processing requests to the report processor and by reducing the time required to retrieve large reports. They have a mandatory expiration period, usually in minutes. For more information about how to use cached reports, see Caching Reports (SSRS).

A clickthrough report is a report that displays related data from a report model when you click the interactive data contained within your model-based report. These reports are generated by the report server based on the information contained within the report model. The person who created the model determines which fields are interactive and which fields are returned when a clickthrough report is opened. These field settings cannot be changed in the report authoring tools.

Clickthrough reports are autogenerated. However, you can create an alternative customized report to the model for interactive data items that is displayed instead. The custom report is a standard Reporting Services report. For more information, see Working with Clickthrough Reports.

Drilldown reports initially hide complexity and enable the user to toggle conditionally hidden report items to control how much detail data they want to see. Drilldown reports must retrieve all possible data that can be shown in the report. For more information, see Hiding and Showing Report Items by Adding Drilldown (Report Builder 3.0 and SSRS).

For reports with large amounts of data, consider drillthrough reports instead.

Drillthrough reports are standard reports that are accessed through a hyperlink on a text box in the original report. Drillthrough reports work with a main report and are the target of a drillthrough action for a report item such as placeholder text or a chart. The main report displays summary information, for example in a matrix or chart. Actions defined in the matrix or chart provide drillthrough links to reports that display greater details based on the aggregate in the main report. Drillthrough reports can be filtered by parameters, but they do not have to be. Drillthrough reports differ from subreports in that the report does not display within the original report, but opens separately. They differ from clickthrough reports in that they are not autogenerated from the data source, but are instead custom reports that are saved on the report server. They differ from drilldown reports in that they retrieve the report data only for the specified parameters or for the dataset query. For more information, see Drillthrough Reports (Report Builder 3.0 and SSRS).

A subreport is a report that displays another report inside the body of a main report. Conceptually, a subreport is similar to a frame in a Web page. It is used to embed a report within a report. Any report can be used as a subreport. The subreport can use different data sources than the main report. The report that the subreport displays is stored on a report server, usually in the same folder as the parent report. You can set up the parent report to pass parameters to the subreport.

Although a subreport can be repeated within data regions using a parameter to filter data in each instance of the subreport, subreports are typically used with a main report as a briefing book or as a container for a collection of related reports. For more information, see Subreports (Report Builder 3.0 and SSRS).

For reports with many instances of subreports, consider using drillthrough reports instead.

 

Tags: