Connecting SSAS Tabular to Excel Files in SharePoint

One of my absolute favorite features in tabular models is the ease in which we can do things like discretization and grouping. What used to take days in Multidimensional can now be accomplished in just minutes. Additionally, we can now give full control of these groups to business users to be able to dynamically adjust these groups in Excel, and import these into our tabular models. I did a post a while back about setting up discretization buckets in tabular, but I was recently showing this to a client and the thought occurred to me that we could create a SharePoint library to house these workbooks instead of having to keep them on a fileshare. Placing them into SharePoint allows us to set security, adjust it in the browser, and just get some other awesome sauce into our model. This should be a pretty short post, but it’s really cool so bear with me. 🙂

 

First, create a SharePoint library to house you’re workbook. Truthfully, any library will do but long term you’re going to want to restrict this library down to just a few individuals who can access it. Once your library is created, upload the workbook. Now select the workbook, and go up to Library in the browser options. About halfway across the top of the page, there is an option to “Open in Explorer”

image

 

When you select that, your workbook will open up in windows explorer and you can then grab the connection string. Right click on the file, and go to properties. You want to grab the location section, and then add in the workbook at the end of it. Notice that SharePoint files have an extra section in the filepath that must be there in order for it to pull.

image

Copy it, and move over to tabular. In the tabular model, create a new connection to an excel workbook, and then paste the filepath you just copied in. Click through and you’re good to go. Now when users want to modify groups they can do so easily from within SharePoint, and you’re tabular model can just pull that data through. Awesome-sauce!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s