Thursday, January 15, 2015

Configure Excel Services in SharePoint 2013

http://prabathf.blogspot.com/2012/12/configure-excel-services-in-sharepoint.html

Configure Excel Services in SharePoint 2013

Finally I got some time to configure my SharePoint 2013 farm. So I thought of using the chance to update my blog too. "Two birds with one stone".
Central Administration > System Settings > Manage services on server
Start "Excel Calculations Services"
Once it's started the next step is to create a new instance of "Excel Service Application" (in one doesnt exist already).
Central Administration > Application Management > Manage service applications and create  a new "Excel Service Application"

You are done with configuring Excel Servcies. As the next step you need to configure the tusted locations where Excel Service will only load Excel Workbooks from.

Central Administration > Application Management > Manage service applications > Excel Servce Application (This will change based on how you named the servcie instance on the previous step) > Trusted File Locations
Fill the Address with a Document Library location. I have created a document library called "Excel Documents" in a site called "Team".
In the External Data section you have "Allow External Data", select the Trusted data connection libraries and embedded option.

Set both the Automatic refresh and Manual refresh values to 0 (But this is not recommend as it will consume more resources by refreshing workbook results immediately).
Now as we have a trusted file location, we can now publish data-connected workbooks with embedded data connections. But for Excel Services to correctly render data-connected workbooks with external data connections, we must configure a trusted data connection library.


Central Administration > Application Management > Manage service applications > Excel Servcie Application > Trusted Data Connection Libraris
Make sure you have a "Data Connection Library" type library. Name of my data connection library is "Data Connections" which is created in a site called "Team".
Now you have successfully completed with Excel service configuration.

----------------------------------------------------------------------------------------------

http://sharepointknowledgebase.blogspot.com/2011/12/this-workbook-cannot-be-opened-because.html#.VLg7CivF-4I

This workbook cannot be opened because it is not stored in an Excel Services Application trusted location

One of our user has opened a ticket with us by mentioning that he is getting an error while editing the date in excel. the error message is mentioned below:

This workbook cannot be opened because it is not stored in an Excel Services Application trusted location.
To create an Excel Services Application trusted location, contact your system administrator
Now its quite obvious that you need to add the document library path inside excel trusted file location.

Please refer following article and your issue will be resolved J
http://sharepointknowledgebase.blogspot.com/2011/12/excel-services-sharepoint-2010-trusted.html

If you have any queries/questions/doubts regarding the above mentioned information then please let me know. I would be more than happy to help you as well as resolves your issues, Thank youJ
- See more at: http://sharepointknowledgebase.blogspot.com/2011/12/this-workbook-cannot-be-opened-because.html#.VLg7CivF-4I

-----------------------------------------------------------------------------------------
http://blogs.technet.com/b/chrad/archive/2010/07/21/tfs-dashboards-in-moss-2010-return-access-denied-for-tfsolapreport-to-end-users.aspx
TechNet Blogs » Chris's Blog - Just weedin around Microsoft » TFS Dashboards in MOSS 2010 Return Access Denied for TfsOlapReport to End-Users

TFS Dashboards in MOSS 2010 Return Access Denied for TfsOlapReport to End-Users

ChrAd 
21 Jul 2010 2:50 PM 
If you’ve decided to integrate your TFS 2010 infrastructure with Microsoft’s enterprise-ready SharePoint Server 2010 (MOSS) then you are in for quite a store, both good and potentially bad.  MOSS is extremely powerful platform that many customers of ours are investing in and we certainly understand why.  However, the frustration often comes when you have competing heads – the TFS infrastructure “guys” and the SharePoint “gals – trying to get all the pieces working and playing together well.
In today’s post, I will share some insight that had our TFS consultants scratching their head and our MOSS folks (me – and can I say I’m limited) spinning around and saying “Just make it work…”  The beauty of working at Microsoft is the ability to find the right people and get the assistance needed to make it work.  This is the reward we get and that reward I’m happy to share with those of you that run into the same thing.
Goal
The goal here is to get TFS integrated fully into MOSS 2010 and, in particular, using Excel Web Services to display critical data in the TFS Dashboards.

Understanding Excel Web Services & TFS Integration

John Socha, a colleague and friend of mine, helped me quickly understand how the TFS Excel reports work with respect to TFS.  Excel workbooks have “cached” data embedded within the XLSX files themselves that is accessible via the Excel or via Excel Web Services.  By default, Excel Web Services (EWS) utilizes a SharePoint feature called Secure Store Service (or abbreviated often as SSS) to allow individuals who do not have direct access to the data source of the workbook to see the data within EWS.

Access Denied by External Data – TfsOlapReport

The reports start coming in as soon as you roll out TFS 2010 that you are having issues with “reports” showing up on the dashboard for each of your team projects.  TFS will create the dashboard anytime you point it towards the MOSS server which is helpful though it doesn’t mean that everything is good to go.
clip_image001
As you can see, the user will see the above error report every time they re-attempt to connect to the site and view the data.  If they click OK, you (and the user) are shocked when data is displayed to the end-user like below.
DynamicImageProviderWhy is it saying I’ve been denied access?

Excel Workbook Caching versus Excel Web Services

The reason data is shown after clicking ok is the user has been granted access to the SharePoint site and hence can access the physical Excel workbook.  As mentioned earlier, Excel stores “cached” data from the last time that the workbook is refreshed and this is shown to the user.
You get Access Denied because the end-user has been denied access to the data source, not the entire workbook.  EWS attempts to access the data source directly each time the site is opened and an existing connection isn’t open.  To validate whether you have an issue with your SSS, you can do the following-
  1. Open your MOSS site hosting TFS content
  2. Click the Excel Reports in the land-hand navigation
  3. Click on the name of the Excel report such as Burndownimage
  4. For Data, click Refresh All Connectionsimage
This will verify if your SSS is configured to work as expected, and that within the Members for SSS you’ve granted that user permissions.  If you get an access denied, let’s walk through fixing this problem.

Determining EWS Configuration – One Workbook at a Time

The first step is to verify that your Excel workbook is using the authentication type.  TFS 2010, out-of-the-box, uses SSS and setups a Target Application ID called TFS.  This, of course, is configurable though you need to think about two things -
  1. SSS doesn’t require end-users to have direct access to the TFS cube (TFS_ANALYSIS)
  2. Windows Authentication requires direct access permissions to the TFS cube
SSS is optimal long-term (as put by someone who helped me figure this out) though it requires a bit more configuration than Windows authentication.
To determine which authentication a workbook is using (if necessary), do the following -
  1. Open the workbook in Excel (from SharePoint, not Visual Studio)
  2. Highlight the chart in the report by clicking on it
  3. Under the Data menu item, Connections
  4. Highlight TfsOlapReport, click Propertiesimage
  5. Click Definition tabimage
  6. For Excel Services, click Authentication Settings…image
As you can see, you can select SSS or you can choose Windows Authentication.  In this case, it should be set to the default of SSS – SSS ID:  TFS.  If not, change it to look directly like the above.

Correctly Configuring Secure Store Service (SSS)

If your workbook is configured correctly, and you are still getting access denied, then you will need to use the SharePoint Central Administration Site to correct the problem.  In this section of the post, I will share specifically how I corrected the problem that was occurring throughout our dashboard.
  1. Open the MOSS Central Administration Site
  2. Click Application Management
  3. Under Service Applications, click Manage service applications
  4. In this list of service names, click Secure Store Applicationimage
  5. You should see a Target Application Id of TFS (if not, you need to create one which isn’t the purpose of this article). click on TFS
  6. On the Target Application Settings page, click Nextimage
  7. On the Field Name page, click Nextimage
  8. On the Administrators & Members page, you will need to enter all potential dashboard viewers into the Members box.image
  9. Click OK
After you’ve clicked OK and saved, you can re-test and the users should not get the Access Denied if they are listed in a domain group that they are a member of or you’ve granted them direct access via their user account.

Summary

Doing software development is hard enough, let’s not make it any harder than it has to be.  Enter the handy dandy capabilities of SharePoint to make flashy, useful dashboards to give insight in how your team is executing and most importantly struggling.  Then it doesn’t work.  This is a challenge set of applications coming together as a “mash up” that one simple step missed can cause it to come tumbling down.  There is documentation out there that can help “lead” you to the problem but unless you know a)  TFS  b)  Excel Web Services  c)  SharePoint then your life is probably going to be a bit complicated.  In today’s post, I shared some insight into one problem that many of you might very well have and hopefully will make your life much better.
Enjoy!
-Chris

Posted by at 9 comments:

Friday, January 9, 2015

text area trick

http://www.impressivewebs.com/textarea-auto-resize/

Textarea Auto Resize

On a current project, I was trying to find a way to auto-resize a textarea according to some content that would be loaded in dynamically via Ajax. I didn’t know the height of the content and the textarea element doesn’t resize naturally like other HTML elements, so I needed to update the height of the element with JavaScript each time the content changed.
It seemed like a simple task. After doing a search to see what types of plugins and scripts were floating around to do this, the examples I found seemed a little overly complex. While most solutions seemed to incorporate some complex math calculations, I thought of a better way.

Using a Hidden Clone Element

A <div> element will naturally stretch to fit the height of its content (assuming no floats or absolutely positioned elements are involved). So to get the height of the textarea, I just need to do the following:
  • Grab the content loaded into the textarea
  • Create an invisible clone div
  • Give the clone the same width and typographical properties as the textarea
  • Place the content into the clone
  • Get the height of the clone
  • Apply the height of the clone to the height of the textarea

The Code

One of the keys to this solution is the CSS. As mentioned, the invisible clone needs to have the same typographical properties as the textarea. Not only does this include stuff like font-size and font-family, but also the white-space and word-wrap properties of the clone need to be set to mimic what happens inside the textarea.
First here’s the CSS for the textarea:
textarea {
    width: 500px;
    min-height: 50px;
    font-family: Arial, sans-serif;
    font-size: 13px;
    color: #444;
    padding: 5px;
}

.noscroll {
    overflow: hidden;
}
Take note that I’ve added a separate class with overflow: hidden, to prevent scrollbars from appearing. Normally this would not be a good thing to add to a textarea element, but because I’ll be resizing it with JavaScript, it’s fine. This class will be added to the textarea with JavaScript, to ensure that if JavaScript is turned off, the textarea will scroll normally.
Here is the CSS I’ll be applying to the hidden clone element:
.hiddendiv {
    display: none;
    white-space: pre-wrap;
    width: 500px;
    min-height: 50px;
    font-family: Arial, sans-serif;
    font-size: 13px;
    padding: 5px;
    word-wrap: break-word;
}
A quick break-down: First, I set it to display: none because I don’t want it visible to the user. I believe this should be fine for screen readers, because I don’t want it read out to them either. If anyone has a better solution for hiding it for assistive devices, let me know.
I’ve also set the white-space property to a value of “pre-wrap”. This ensures that lines will wrap correctly, but everything else gets pre-formatted. I’ve also set the width to be equal to the textarea, and duplicated a few typographical properties. In both examples, I’m giving the clone and the textarea a min-height so it will always start out at a standard, usable height.
Now for the JavaScript (which is using jQuery, sorry):
$(function() {
    var txt = $('#comments'),
        hiddenDiv = $(document.createElement('div')),
        content = null;

    txt.addClass('txtstuff');
    hiddenDiv.addClass('hiddendiv common');

    $('body').append(hiddenDiv);

    txt.on('keyup', function () {

        content = $(this).val();

        content = content.replace(/\n/g, '<br>');
        hiddenDiv.html(content + '<br class="lbr">');

        $(this).css('height', hiddenDiv.height());

    });​
});
This code assumes we’re targeting a single textarea element on the page. If you need this to affect more than one element, then just change the first line inside the function that defines the element we’re working with.
I’m dynamically changing the height based on jQuery’s keyup event. You could easily change this to respond to an Ajax request instead, if you happen to be loading the content that way.
Using keyup, however, is a good solution because it’s the most likely reason that you’ll want to auto-resize a textarea — user-entered data.

What About IE6-8?

I almost didn’t write this article, because the code wasn’t working at all in IE6-8. The reason for this had to do with the poor way IE handles grabbing content using innerHTML. So after I had written this simple solution that seemed to work in all the newer browsers, I came across this jQuery plugin. That solution uses the exact same method that I’m using (the cloned element), and it worked (mostly) in IE.
So the line that I borrowed from that example that (mostly) fixed mine in IE was:
// fixes the IE innerHTML problem
content = content.replace(/\n/g, '<br>');
But even after adding this line, there was still an issue: Long, unbroken strings of text wouldn’t affect the height of the textarea (which isn’t a big problem, really). A simple fix was adding word-wrap: break-word to the CSS for the clone element.

Bugs? Problems?

Like the other solutions floating around, this could easily be turned into a plugin. In that case, I’d have to add a little more jQuery so that the characteristics of the clone element aren’t dependent on the CSS. Also, if the width of the textarea is fluid, then you’d have to use jQuery to grab that, and then apply it to the clone.
The solution I linked to in the previous section is just about perfect — you just need to add the word-wrap fix that I mentioned.
For a demo, you can view my JSFiddle using the link below. Just be sure to hit the “run” button before you test it. Let me know if you find any problems with it.
Update (Aug. 14/2012) The demo link above now points to an updated version with some of the fixes suggested in the comments along with some CSS improvements.

Posted by at 2 comments:

Friday, December 26, 2014

How to restore backup by piece meal


Quoted from http://blogs.msdn.com/b/sqlcat/archive/2005/10/27/how-can-sql-server-2005-online-piecemeal-restore-improve-availability.aspx

Question 1:  How can SQL Server 2005 OnLine Piecemeal Restore improve availability?
Question 2:  Can I recover a single object such as a table or specific partition(s) from a partitioned table?
Answer:

The question is how to get the greatest flexibility, manageability, availability and recoverability for large databases. Anytime you need to restore a database to a specific point-in-time, all filegroups in that database must be restored to the same point-in-time.  Restoring an entire multi-terabyte database to a point-in-time could result in a big outage, an especially severe penalty if the data to be restored is relatively small compared to the entire database.  While SQL Server 2000 provided filegroup restores, all filegroups must be available for the database to be accessible. 

To provide greater flexibility for large DB scenarios, a new feature of SQL Server 2005 called OnLine Piecemeal Restore allows us to use specific filegroup backups to create a smaller work database containing only the data we wish to restore. By placing tables (including individual partitions of partitioned tables) on specific filegroups, we can devise a granular backup and restore strategy where part of a database such as a table or even a single partition of a partitioned table, can be restored quickly.   We can also roll transactions forward to a specific point-in-time with transaction logs using any of the following: a specific time, a transaction mark, or a log sequence number (LSN).

The example database, ProdDB has the following secondary filegroups: 
·        Customers:                                    FGCustomers
·        Current year orders (2005):         FGOrders
·        Historical orders (2000-2004):     FGOrderHistory. 

ProdDB contains three large user tables:  Customers, Orders and OrderDetails.  The Customers table is placed on filegroup FGCustomers.  The table Orders is partitioned by year.  The current years’ orders (2005) are placed on FGOrders, while years 2000-2004 are placed on filegroup FGOrderHistory.  The layout of ProdDB is as follows:


Primary filegroup
FGCustomers (RW)
FGOrders (RW)
FGOrderHistory (RO)
Contains system tables only, no user tables
Customers
Customers data
Orders & OrderDetails
2005 Orders partition

        OrderDetails
OrderHistory
2000-2004 Orders partitions

Customers

The combination of filegroup backups and rolling transaction logs forward provide recoverability to a point-in-time. Assume that a batch job has resulted in incorrect 2005 data and that you must perform a partial filegroup restore of FGOrders (shaded in gray) to recover the data.

Steps for partial online filegroup restore to a point-in-time:
  1. Backup ProdDB’s filegroups primary, FGCustomers, FGOrders, FGOrderHistory
  2. Batch jobs run (assume batch job messes up 2005 data……)
  3. Backup tail of transaction log (assuming you need to roll forward to point-in-time in the log)
  4. Perform piecemeal restore. Use ProdDB primary & specific filegroup (FGOrders) backup to create a new work database called PartialDB. Roll forward transaction log to point in time (just before bad batch job). The 2005 partition of Orders was recovered from the FGOrders backup.
  5. Delete bad data in ProdDB. 
  6. Copy restored partition or table from work database PartialDB to ProdDB

Example of filegroup & transaction log backups, bad batch job, and online piecemeal restore

1. Backup ProdDB filegroups & transaction log:
BACKUP DATABASE ProdDB
FILEGROUP = 'PRIMARY'
to disk = 'C:\ProdDB_FG_PRIMARY_SQL.BAK' with init
-- SQL FGCustomers FILEGROUP backup
BACKUP DATABASE ProdDB
FILEGROUP = 'FGCustomers'
to disk = 'C:\ProdDB_FG_FGCustomers_SQL.BAK' with init
-- SQL FGOrders FILEGROUP backup
BACKUP DATABASE ProdDB
FILEGROUP = 'FGOrders'   
to disk = 'C:\ProdDB_FG_FGOrders_SQL.BAK' with init
-- SQL FGOrderHistory FILEGROUP backup
BACKUP DATABASE ProdDB
FILEGROUP = 'FGOrderHistory'   
to disk = 'C:\ProdDB_FG_FGOrderHistory_SQL.BAK' with init
-- SQL log backup
BACKUP log ProdDB
to disk = 'C:\ProdDB_LOG_SQL1.BAK' with init

2.      Assume a batch job messes up some 2005 data
-- assume a batch job wrongly updates or deletes some 2005 data
DELETE from [ProdDB]..Orders
where OrderID in (select top 50 OrderID from [ProdDB]..Orders where OrderDate > '2005-01-30')

3.      Backup tail of log:
-- Backup tail of transaction log (assuming you need to roll forward to point-in-time in the log)
BACKUP log ProdDB
to disk = 'C:\ProdDB_LOG_SQL2.BAK' with init

4.      OnLine Piecemeal Restore creates work database PartialDB:
-- PartialDB will be created from ProdDB backups.
-- MOVE used to create files for PartialDB
RESTORE DATABASE PartialDB
from disk = 'C:\ProdDB_FG_PRIMARY_SQL.BAK'
WITH NORECOVERY, REPLACE, PARTIAL – partial:not all FG will be restored
,MOVE 'ProdDB_data' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBPri1dat.mdf'
,MOVE 'SProdDB_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBlog.ldf'
-- restore only the latest 2005 data, oldest partitions on FGOrdersHistory not restored
RESTORE DATABASE PartialDB
from disk = 'C:\ProdDB_FG_FGOrders_SQL.BAK'
WITH NORECOVERY
      ,MOVE 'FGOrders_file1' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBOrdFi1dt.ndf'
-- restore first log up to point-in-time. You can also restore to a transaction mark or LSN.
RESTORE log PartialDB
from disk = 'C:\ProdDB_LOG_SQL1.BAK'
WITH NORECOVERY, STOPAT = 'Oct 26, 2005 11:00 PM'
-- restore second log up to point-in-time. You can also restore to a transaction mark or LSN.
RESTORE log PartialDB
from disk = 'C:\ProdDB_LOG_SQL2.BAK'
WITH RECOVERY, STOPAT = 'Oct 26, 2005 11:00 PM'

-- NOTE: Piecemeal restore. Part of PartialDB is recovered & online.
select file_id, name, physical_name, state, state_desc
from sys.database_files

Note if you choose to use a transaction mark to establish the point-in-time to recover to, remember that the transaction must be writable, e.g. it must contain an insert, update, or delete within the transaction for the transaction mark to be written to the log.  A transaction mark with select statements only is not a writeable transaction.

Note that the newly created work database PartialDB contains only 3 files from 2 filegroups. A backup of the Primary filegroup restores the files ProdDB_data (contains system catalog tables) and SProdDB_log which is the transaction log. The secondary filegroup FGOrders is used to recreate FGOrders_file1.  FGOrders contains only the 2005 Orders data.

File_id
Name
Physical_name
State
State_Desc
1
ProdDB_data
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBPri1dat.mdf
0
ONLINE
2
SProdDB_log
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBlog.ldf
0
ONLINE
3
FGCustomers_file1
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ProdDBCustFi1dt.ndf
3
RECOVERY_PENDING
4
FGOrders_file1
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBOrdFi1dt.ndf
0
ONLINE
5
FGOrderHistory_file1
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ProdDBOrdHistFi1dt.ndf
3
RECOVERY_PENDING

PartialDB special notes:
The primary filegroup contains the system tables that define objects for the database.  As a best practice and to expedite the restore process, it is suggested that user objects are placed on secondary filegroups, not the primary filegroup.  Once the primary filegroup is restored, the system tables contain the metadata for all objects in the database, although you may not be actually restoring all objects.

While you can select data from any ONLINE filegroup, remember that some objects such as a partitioned table, can span filegroups.  If some but not all partitions of a partitioned table (such as in the example above) have been recovered, an unqualified SELECT * FROM Orders, or any other object that has not been recovered will likewise fail as follows:

Msg 679, Level 16, State 1, Line 1
One of the partitions of index '' for table 'dbo.Orders'(partition ID 72057594038452224) resides on a filegroup that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result.

5.      Delete bad data in ProdDB.
Before restoring the data in ProdDB , you must remove the bad data in ProdDB using various techniques such as
a.        drop table (if you plan on using select into to recreate the object)
b.        truncate table to remove all rows or delete to remove specific rows using a WHERE clause
c.        switch out (for partitioned tables).  A partition must be empty (using switch out) before you can switch the new data in. Once switched out, you can drop the bad data.

6.      Copy partition or table from PartialDB to ProdDB.
Use copy techniques to restore the data in ProdDB.   Recreate indexes on the copied object, add any constraints, etc.  Some copy alternatives to consider:
a.        select * into ProdDB..OrderDetails from PartialDB..OrderDetails  (if you are restoring the entire table, drop table in ProdDB first)
b.        insert into ProdDB..Orders select * from PartialDB..Orders where OrderDate >= '2005-01-30'
c.        create work table on correct filegroup, copy data, switch in (for partitioned tables).  Note: The definition of the work source table and target partitioned table must match exactly. The target partition must be empty.  The source table (used in the switch in) must be on the same filegroup as the partition. 

If you restore an object to a specific point-in-time, you may have to consider the effects on referential integrity (RI).  You may have to perform RI checks to ensure relationships are intact.

Summary:
In conclusion, SQL Server 2005 provides a flexible filegroup backup strategy that should be especially helpful in large database scenarios. By placing tables (including individual partitions of partitioned tables) on specific filegroups, we can devise a granular backup and restore strategy where we can restore part of a database quickly to a smaller work database. Using the techniques described above, we can recover entire tables or specific table partitions.  Combined with transaction marks, we can restore to a specific point-in-time.  After the object has been restored to a work database, the last step is to copy it to the production database.  After the copy, the work database can be dropped.

Tom Davidson
SQL Server Customer Advisory Team
Posted by at No comments:
Subscribe to: Posts (Atom)