Posted on

SharePoint 2013 and SQL Powered BI

I know i have been AWOL now for some time… i have been heavily focused on bringing up Sharepoint 2013…and also empowering it with SQL 2012. And, actually there’s even more to it than that. One of the benefits of 2013 is that Microsoft has extended the value proposition by enabling the creation of a public site w/o real cost. Not only that…Microsoft says that you can access your SSAS components from that public site. Ok…think about that. That’s really big! That’s what we collectively thought as well.

HOWEVER, there are some caveats. Actually there are quite a few caveats that you can easily stumble into. Going forward, i expect to blog about these as we find them.

The first I’m going to mention has to do with Anonymous access to an excel file. It turns out that while other users aren’t limited (so far as I know today), Anonymous user can’t browse excel files greater than 10 MB! So, for context, we had built a PowerPivot based excel with some 180 MB of data. We published it to the sharepoint 2013 site. We went to the site ourselves (ie. IE knew our identity and passed it to SharePoint. Sharepoint showed us the file in a browser. Yeay! Success!

Hold your horses…check with anonymous! We anonymously logged in to the sharepoint public site, navigate to the Excel file, attempt to open, and ERROR! Arghh! Why would Microsoft do this? Well, it’s money, of course!

See here for more Microsoftian data:

http://office.microsoft.com/en-us/office365-sharepoint-online-enterprise-help/file-size-limits-for-workbooks-in-sharepoint-online-HA104099212.aspx

 

More to follow!

Posted on

SQL Saturday Jacksonville, May 10, 2014

sqlsat298_webHey, everyone! There’s a great event here in Jacksonville, Florida. It’s coming in May. It is like a mini Tech-ed. Free Food. Great Technical presentations.  Click the picture to the right to register!

Lots of networking. Fellowship. Swag. After hours party. What could be better than that? I will be speaking on a “How SQL 2012 Empowers BI in Sharepoint”.

Posted on

Tabular logging of Users is not like Multidimensional!

A few days ago, I wanted to start tracking on who was using my Tabular solutions. (If you wandered into this post… I’m talking about Microsoft’s SQL server Analysis Services.) With Multidimensional you have a table, that given the right circumstances, Look here: “SSAS not logging?” will track users of your Multidimensional solutions (think “cubes”…that’s what we used to call them). Well, Tabular has all the administrative set up as does Multidimensional… meaning right click on the tabular instance inside SSMS, and you’ll be given options wherein you could set up logging of users touching your Tabular solutions. WOW! GREAT! Just what i wanted…and just what i expected.

BUT! It didn’t work! And, it won’t work! Why? Because the Logging on Multidimensional models was originally conceived for the purpose of an optimization wizard…wherein, user queries would help guide what should be pre-aggregated. Well, Tabular, using Vertipaq doesn’t do any pre-aggregation. So, they have disabled that logging function. Yes, you CAN set it up. And yes, the table does get built in wherever you designate…but, NO it never gets filled in with details of users’ usage. Go figure!

Posted on

SSRS Data Source Lister

Hello,

Sometimes, you’re in the middle of doing something, and you need an answer.  You google it and find such a great solution, you want to keep it handy for … whenever!  Here is such a post.  As the title of my post indicates, you can list out the Data Sources that exist on an SSRS server… and you can do so with this code…which i got entirely from http://gallery.technet.microsoft.com/scriptcenter/List-connection-strings-of-1a9a9adc.  Thank you Olaf Helper!

 

Posted on

Reading an SSAS cube thru SQL using MDX

Do you need the ability to read a cube, but you want to read it thru SQL?  And, why would you do this?  Well, for example, suppose you wanted to email the results of a certain MDX query…and you didn’t want to set up SSRS to do this.  (SSRS would be a fine method, but you do have to set it up and create the report etc.)  Suppose you were simply more comfortable using a SQL Server Agent job to do this task for you.  Continue reading “Reading an SSAS cube thru SQL using MDX”

Posted on

SSAS not logging?

So you’ve followed all the advice and set up logging so that you can know who is using your cubes.  Things are good.  One day you wake up and see that your logs no longer are logging.  You open your cube yourself and browse.  Then you re-check your logs…no entries.  You KNOW the cube is working.  You KNEW the log WAS logging.  Why not now?  Continue reading “SSAS not logging?”

Posted on

Sql Saturday Jacksonville, April 27 2013

Hey, everyone!  There’s a great event here in Jacksonville, Florida.  It’s coming in April.  It is like a mini Tech-ed.  Free Food.  Great Technical presentations.

BI Consultants toolkit
Click here to register!

Lots of networking.  Fellowship.  Swag.  After hours party.  What could be better than that? I will be speaking on a “BI Consultants Toolbelt”.  Free tools that any BI consultant should at least be aware of.  In fact they are the focus of my last two blog entries…plus they’ll be hilighted in the upcoming blog entries.

 

Posted on

ASSP.dll…super great functionality

There is a “DLL” that you are going to want to install on your SSAS servers… it is called “ASSP.DLL”…and you can get it from Code plex.  To quote CodePlex:

The Analysis Services Stored Procedure project is a set of sample stored procedures for Analysis Services 2005, 2008, 2008 R2, and 2012. These samples have been written in C# and sample MDX queries are included that demonstrate the use of the procedures against the Adventure Works DW sample database. It was developed by a group of community volunteers with 2 main aims in mind:

  1. To provide a set of useful extensions to Analysis Services 2005 and higher.
  2. To provide a variety of example source code for people looking to write their own stored procedures.

So, go to CodePlex, follow their instructions and install the DLL on your server. After that is done, you can try the code below…