We installed Panorama Nova View Flash 6.2.5 and wanted to use it to connect to PowerPivot. While beginning the work, we couldn't connect
to the PowerPivot files and would get an error message. My colleague Tomer Horev was in charge of SharePoint installation and also found the following solution.
We understood what the problem was through the help of Panorama support. We tried to connect to the cube that's created for our PowerPivot file through the Management Studio. That meant connecting to the SSAS DB with the name of:
http://servername/foldername/filename.xlsx
We failed to connect and got a 401 error message of:
401 - Unauthorized: Access is denied due to invalid credentials.
We understood that the application didn't recognize the user.
Since the user could access and view the PowerPivot file and the PowerPivot cube, we could assume the problem is with the connection between the file on the SharePoint Server and the cube on the PowerPivot SSAS Server. That meant checking the authentication definitions for the PowerPivot service on the SharePoint server. We found the following post about problem connecting to PowerPivot from Report Builder or Excel. That had the solution to our problem as well!
Go to the config file of the PowerPivot service: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\isapi\PowerPivot\web.config
There you need to change all 4 basicHttpBindings at the top to clientCredentialType = "Windows" and also changed the 2 customBindings to authenticationScheme = "Negotiate".
After that, we could create Panorama views on top of our PowerPivot on SharePoint cube.
The daily Excel data update worked fine until… one day it didn't. Obviously it's just a few days before a presentation to our head of department :)
My colleague Tomer Horev, who's in charge of the SharePoint end, was trying to find a reason for the connection failure. The error message was:
The refresh operation failed because the source data base or the table does not exist, or because you do not have access to the source
More Details:
OLE DB or ODBC error: Unexpected error from external database driver ().; 3275.
A connection could not be made to the data source with the DataSourceID of 'f3b6242a-ff67-4e84-8f20-847861d6e7ab', Name of 'Excel XYZ'.
An error occurred while processing the 'DATA' table.
The current operation was cancelled because another operation in the transaction failed.
Out of line object 'DataSourceView', referring to ID(s) 'Temp_DSV', has been specified but has not been used.
A link for Unexpected error from external database driver helped him understand that he needed to add Imex=1 in the connection string.
Just look under the Design tab for Existing Connections > choose the relevant connection > click the Edit button and then the Advanced button:
So, a failed data refresh for an Excel 2010 on SharePoint 2010 can sometimes be a broken PowerPivot connection.
Microsoft recently released a CTP of Power View on SSAS Multidimensional, (and yes, I know I'm a bit late writing about this, but I still wanted to give my two cents on it). So we're edging closer towards the point where Power View and Excel will be able to cover all of your viewer needs (or at least most of them :)).
Years ago when I wrote about Report Builder on a cube and all the problems that that relationship created, I wondered when that would be fixed. Now that we have Power View, I'm glad that Microsoft is trying to make it a complete solution, adding layers to it gradually so that hopefully the end solution will be with as less "features" as possible. What I was most happy to read was that in Power View on SSAS multidimensional (aka DAXMD) the DAX queries are handled natively on the server and that the performance is good.
If you'd like to see the how the future of SSRS and SSAS looks like, I recommend you give the CTP a look. And if you'd like to try and influence the future development of SSAS Reporting please fill in the survey the SSAS team is conducting.
I have recently been to the last BI User group which hosted a lecture from Mr. Dany Hoter, Senior Program Manager at Microsoft. Last time I saw Mr. Hoter speak was about Excel 2007. Yes, it's been a while. But I dare say that the fact I remember his lecture, speaks volumes on how much I enjoyed hearing him. This time around, Mr. Hoter spoke to us about Excel 2013 (how time flies...)
The lecture itself was filled with demos Mr. Hoter did and showed on an Excel 2013 workbook he put at an Office 365 site, which is at the moment open to selected members.
Office 365 is currently available for a trial download. It includes amongst other things: SharePoint, Exchange, Lync and more. The trial version gives you the chance to share your SharePoint site with up to 25 users. The SharePoint 2013 look and feel will be similar to that of the online version.
First and foremost, Mr. Hoter stressed the importance of using tables in Excel. Tables give you a lot of added functionality and shouldn't be passed on when trying to analyze your data.
Throughout the lecture, Mr. Hoter tried to show us the new advantages to Excel 2013:
-
Under the Data tab, you'll find
Flash Fill. After filling two cells in relation to their adjacent cells, Excel tries to find the logic and the connection between the cells and updates the rest of the column. We can also update what's written for the third cell and excel will update the logic for the rest of the column.
-
In Excel ProPlus edition (the Enterprise Edition of Office 2013) you also get the
Inquire tab. In that tab you can find the Compare Files wizard. We can choose two versions of the same file and the compare files will show us what was added, deleted or changed. It will compare not just the text in both files, but also the formatting and the macros.
-
Under the Insert tab we can also see the option for
Recommended Charts. The recommended charts then gives you a preview of the charts it finds to be best suited for your data. So you'll be able to choose the best visualization for your data.
-
You have pivot on OLAP data and new to Excel 2013 you can now add an MDX calculation at the pivot level itself. From 2010 you can define named sets for re-use of groups in your data sheet.
-
A new GUI was put into Combo Charts helping you build and change easily the axis and the type of chart you give for each data, again with a live preview for each option.
-
If I created a file but decided not to save it eventually and exited the file clicking on "don't save" (rather than "save") I still have a temporary file for me to return to, in case I regretted my choice.
-
In the past, if I wanted to share with my colleagues a file which had PowerPivot, they had to have PowerPivot installed on their PC in order for them to look into it. Installing that add in was a problem in some organizations and the only other solution was to have SharePoint in the organization (which was also problematic at times). In Excel 2013 PowerPivot and Power View are integral parts in the Excel itself.
You have to "turn on" PowerPivot though File > Options > PowerPivot enable
-
When adding tables to your PowerPivot, through the Data Connection Wizard, you can choose Enable selection of multiple tables (a new choice for Excel 2013)
If you're pulling tables from the SQL Server into PowerPivot, if you defined in the DB a primary key - foreign key relationship, then PowerPivot can detect that and it will be uploaded into the DSV with those connections.
It's recommended to load tables and data into the PowerPivot and not to the Excel itself. Loading data into the Excel makes the file bigger and takes longer to run than when it's loaded to PowerPivot.
I can edit table properties before and after loading them. This enables me to filter out records and chose which columns to load. In fact tables with millions of rows this is especially important as cost more in size. A column that is unique costs more, and so a primary key of a fact table should best not be included. This is especially important as SharePoint Online, (SharePoint in the cloud), supports files up to 10M for opening and editing online in the cloud (if I'm only interested in storage, then I can upload bigger files).
Power View automatically shows us all the charts and pivot tables connected as they are all based on the same model. That way, if we choose a product category in one chart, it will affect everything else that's showing. If we want something that isn't affected to show, we need to define with DAX a measure which is detached of that dimension.
You can upload in your Excel files certain spreadsheets and parameters and not necessarily the entire file. On the other hand when loading a file with PowerPivot, all of the data will be uploaded (with no ability of hiding or filtering out data).
During his lecture Mr. Hoter spoke of 2 things in development:
- Power View is currently being developed to work over OLAP cubes! Still, he wouldn't say when that will be shipped :)...
- The use of regular Excel files will be open for everyone in SharePoint Online. The use of Excel files with BI capabilities in SharePoint Online will be under a specific license and will require payment.
- Microsoft itself is moving from SharePoint to SharePoint Online, as they are trying to move all their activity to the cloud and move away from the local servers.
So in conclusion, it was a pretty thorough lecture on Excel 2013, PowerPivot, Power View and Office 365. If you'd like to see a bit of what's been demoed, have a look at Mr. Sean Boon series of videos:
I've decided to make a post to round up all the learning days of MOC 50429 SharePoint 2010 Business Intelligence. Hope this somehow helps you reach all the data:
I would also like include for all us beginners videoes with basic steps on all the services you have for BI in SharePoint 2010.
So the price is still right - and yes, it's free! Marina my colleague from work sent me a really great link so obviously I had to share it with all of you. In his blog about Microsoft SMS&P Partner Community Blog, Mr. Eric Ligman has published a very extensive list of free Microsoft eBooks on SharePoint, Visual Studio, Windows Phone, Windows 8, Office 365, Office 2010, SQL Server 2012, Azure, and more. The list is very comprehensive and I recommend you go over it and also look at the additional post he's linking to for even more eBooks and resource kits.
I have recently posted an article in Silicon India comparing SQL Server 2012 BI tools and means of development. I addressed there the tools in SQL Server 2012: SSRS, Report Builder 3.0, Power View, Tabular and Multidimensional Analysis Services.
Though I tried to cover much ground in the article, I would still recommend some additional reading: -
-
In Report Builder 3.0 we can use report parts that exist on the Report Server. In Reporting Services in Visual Studio, we can publish report parts but not consume them.
You should build a basic report on your PC and deploy it to the relevant SharePoint folder. You should then build a new connection on the SharePoint server and connect the report to the connection on the SharePoint server.
You can build a basic connection in Reporting Services in BIDS or in Visual Studio, and then copy the connection string from there to the connection you’re creating in SharePoint.
SharePoint allows us to create one parameter and use it against different reports in different dashboards.
On your computer, you can look under:
Programs > SQL Server > 10.5 > Reporting Services > Styles
There you can see the CSS files that allow you to style your RS reports. You can take a CSS file, copy it to a different location, give it a new name, change it according to your needs and then put it back again in the Styles folder. We can edit CSS stylesheets in SharePoint Designer.
We can then render our RS report with rcstylesheet=MyStyleSheet.
There are more SSRS URL Access Parameters you can use.
Ajax already exists in SharePoint and so, we can leverage its abilities. We can, for instance, update SharePoint parameters using JavaScript. A filter of SharePoint can refer to something in the page's URL using JavaScript. A PerformancePoint filter doesn't know how to do that.
We can usually see actions in Reporting Services as "href=" in the HTML code of the page of the report. If we'd like, we can use JavaScript planted into the HTML to enhance the abilities of the actions in the report.
We can also use jQuery - an event library for JavaScript.
If we press F12 while we're in a page, we can see the parts the page is made out of: html, css, scripts etc. We can then take them and create a new master page.
Changes that are "out of the box" would be done through CSS (for styling) or JavaScript (for actions).
SSAS will give you better performance on top of a DWH of a few Terras (2+) rather than Tabular mode. That's because of the data summary and compression SSAS does to the data. Tabular is not necessarily faster than MOLAP.
Tabular mode is a new DB engine with 2 possible modes of interaction:
1. Tabular Mode
- Column store
- In memory
- Compression (~1:3)
- All the data copied
2. Direct SQL
- Queries translated into SQL running against the SQL DB
- Serves as Tabular mode's version of ROLAP
You have to choose which version to use when using Tabular. This is in contrast to OLAP which can have in one project partitions of different types (for instance MOLAP for all the history and the ROLAP for the last few days).
Tabular vs. Multi-Dimensional
1. Tabular
- Quicker development
- Manages detail and aggregate data
- Missing feature
2. Multi-Dimensional
- Rich functionality
- Well documented
- Suitable for very large cubes
Read more about this in the white paper: Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services
Tabular mode and Multi- Dimensional can only exist on different instances on the same machine.
You can write queries in MDX against Tabular mode though the engine translates it into DAX, it's not necessarily better for performance to write you queries directly in DAX. (In the MDX window in SSMS we can also write DAX).
In a Tabular project, we build measures, KPIs and hierarchies all in the model which also serves as a DSV. The model can use multiple data sources.
A Tabular model copies all the data into memory. So when developing with it, it's best to develop on small volumes on my own PC. We should load all the data into memory only after deploying to the server.
Also, when adding a calculated column, the SSDT runs the calculation on all the rows in the table, so a quicker run would be on small amounts of data.
If we want immediate updates, we should use direct query mode.
Referential integrity – in case of a value in a fact table without a corresponding key in the dimension, we'll see a member with a null value in the report (the value won't be dropped).
Tabular mode gives better performance for Count Distinct measures.
Large dimensions (of over 1 million members) also perform better in Tabular mode.
There is no role playing dimension in Tabular mode. A table can be connected just once to another table. To bypass this difficulty, we can bring the same table a few times. For instance, we can bring the date table 3 times to connect it to internet sales once for due date, once for ship date and once for sales date.
A hierarchy can be based on one table only. We can also bypass this difficulty by using a calculated column based on a table connected to it (with related()).
For further information, Mr. Braun has made his slides on Tabular mode available.
Last but not least, a short video on how to build a basic Tabular model in SQL Server 2012:
Making the data in access available to everybody – just like with the Excel files in Excel services.
This enables us to manage the data stored in Access through SharePoint 2010 and use the SharePoint UI to update tables and rows.
|
Enables us to create a KPI for each and every level in the hierarchy.
A hierarchy could be for instance an organizational hierarchy, a product line tree etc. etc.
Let’s assume this is a diagram in Visio of an organizational hierarchy
Each circle stands for a unit in the organization
We can give each of the circles a color, making it a KPI
We can also decide that the thickness of the line
connecting the circles will have a meaning according to
how much that unit contributed to its parent unit .
We can give a thicker line with a percentage next to it.
We can also see to it that clicking on each circle will land us
in a detail report of that unit’s sales |
![clip_image001[8]](http://blogs.microsoft.co.il/blogs/barbaro/clip_image0018_thumb_10F82C01.gif "clip_image001[8]")
|
Spatial data can be used in Report Builder 3.0 and Reporting Services 2008 onwards. The maps are based on data of type geometry in the SQL Server DB. We can show colors on the map as a sort of KPI and then drill down on the map for a detailed report of what made up those numbers. The data for the map is in one dataset and the measure for that area is in another and we can connect them through a filed in both datasets.
We can also show events on the map occurring in that area.
We can use map layers with different partitioning of the same area.
We can embed the data for the map in the report if we know in advance about a certain area that will be in use. If that area can change (a new partitioning for areas in the city), we should avoid embedding it so that the shape will be updated every time the report is opened.
We can use xmlt to connect an external data source (such as a SharePoint list data) to the data in a map.
If the entity I’m using is for instance a customer, and the table describing that customer has a long list of columns to describe the customer attributes, I don’t have to show that entity as a regular list. In a list, that customer with all his attributes will be spread out for a very long line. The user will have to scroll to the left and right to see all of the attributes. Instead, we can use an Entity Profile Page which will show a sort of organized card ID for that customer, which would be more readable for my user.
We actually define security in the level of the file. The minute the user has access to the file, he can access all the data stored in it.
To hide code fields from the user in the Pivot table, we need to hide them in the PowerPivot.
PowerPivot allows us to drag to the pivot table attributes and measures which are not necessarily connected to each other.
PowerPivot automatically does referential integrity between the tables connected. We’ll see a “ “ (blank) customer in case I didn’t have a real customer connected to certain sales.
When you add slicers around your pivot table, try adding them in a way that makes them user friendly by turning to basic logic. For instance:
Territory > Country > Region > City (Horizontal slicers)
Year > Pivot Data
Month >
Day of Month
(Vertical slicers)
We can expose PowerPivot reports through the Excel services to the PerformancePoint dashboard.
You can add calculated measures to PowerPivot through DAX.
The bottleneck for PowerPivot isn’t usually with getting the answer for the query we ran, but more in rendering it to html.
Via BI/BPM - The SeeQueL, I have recently learned of a great new site I would like to share with you: MSBIAcademy. MSBIAcademy is a site created by Rob Kerr, SQL Server MVP and CTO at BlueGranite. The site currently offers lessons on: Excel, Integration Services, Data Warehouse Design, SQL Server, Mobile, SharePoint, Analysis Services (both Tabular and Multidimensional) and PowerView! Quite the impressive list. Most lessons are on the latest version of SQL Server, but guide lines given in them are true for earlier versions as well.
I highly recommend you check out MSBIAcademy as in short lessons of about 5 minutes and without registration - you get a good concept of what to do and how things work for every subject. A very impressive initiative!
Business Connectivity enables me to connect to an external data source. Allowing the SharePoint user to update\ delete\ insert rows to a table through the SharePoint UI.
Works well with one table, not with a few tables.
SharePoint Designer enables us to edit page properties, CSS files etc. If we’re writing code, we may prefer to write it in Visual Studio and then paste it into the designer.
We can use it for manual versioning when we copy files from our test area to our production area.
You should avoid editing dashboard pages in the SharePoint Designer as it can lead to breaking connections between web parts and pages.
We need to use F5 key and at times closing a page and re-opening it to see the changes we defined take effect.
We should also take notice to the tabs that are open in the Designer as the Designer enables us to open parts more than once. That could lead to us having a change we haven't saved for a page in one tab, and then another tab where we're editing an old version of the same page. This could lead to one of the tabs not taking effect.
Most of the things that we can edit through SharePoint Designer we can also edit through the web UI. Defining a connection in the BCS can be done only through the SharePoint Designer.
A site of itself to manage the entire front – end sites the users see. Manages sites, pages and services on those sites.
Dear blog, it’s been five years. If to be more exact, it’s been five years on May 15th. And I did remember the anniversary. But I didn’t know what I had to say. I wanted to tell you something meaningful. Make it special. And I didn’t know what I had to say or do that would be anything like that.
I thought to myself I’d do something with the number five in it. Found nothing that felt like it could be good enough. So I thought of something different. My favorite song from David Bowie is Thursday’s Child. And as it’s the fifth day in the week, so I thought I’ll make that our song for this anniversary.
Thursday’s Child speaks of how a good change comes into your life and gives you hope, after a long time of struggling through difficulties. For me, I felt a turn in my life two years ago. And having you blog, through the good and the bad, has strengthened me.
To all the readers of this blog, me and the blog would be very happy to meet you so please leave a comment telling us about yourself :)
And now blog, David Bowie!
I have recently finished MOC 50429 SharePoint 2010 Business Intelligence with Lior Cohen Amikam which I found very enlightening. I thought I’d share with you some of the excerpts from my summaries throughout the course. It’s a mix of ideas of what to do with SharePoint BI abilities and a short description of what they are. This is the second post in a series of posts that I am making from the material of the course.
PerformancePoint Dashboard Designer is the place where we connect the different parts of the dashboard one to another. We build the different reports, (scorecard, filter), and then use the designer to connect them all in one dashboard. The dashboard is only a place to link content which was saved in the different PerformancePoint folder (be it for reports, scorecards etc.)
The minute we save a change to the content (the report or the scorecard saved in the dashboard for instance), we needn’t update anything in the dashboard – the change will show automatically in it.
If we change something in the dashboard itself (adding a new filter to the dashboard for instance), the user will see it only after the deploy of the dashboard.
After deploying the dashboard, we’ll get an .aspx page with web parts with PerformancePoint content type.
Each deploy of the dashboard will overwrite definitions we may have made in between deploys in the SharePoint itself (which is why we should keep them in the definitions to the CSS or Master Page that the dashboard is connected to).
We can use the filter even to let the user decide which columns to show in the report.
We’ll show in each Dashboard a different issue (a separate one for HR, Finance etc.). In each Dashboard we can use a different tab for a different level of granularity and detail.
When we work with the Dashboard Designer, we can save a workspace locally. We can use that as a backup of our dashboard design, or to help us move the dashboard between environments.
I can open a workspace from my local PC and then chose to mark the difference between workspaces to see the difference between my copy and the one on the server. I can also choose compare item to see in more detail the difference. If I want I can save my version over the one on the server or I can click on the added item and choose to remove it from the workspace on my PC and add to it the item from the server. If we keep versioning on the server then we can always go back to an earlier version.
PerformancePoint runs a query for each value it needs to calculate. The query, isn't necessarily the optimal query (a bit like the report builder 1.0 query isn't the best T-SQL you could write). That means that usually calculating a scorecard with a KPI would be very pricey performance wise (a lot of values to calculate). So if you're cube is big and not built by all the best practices + the query of the scorecard isn't optimal, then you would find the scorecard taking a long time to load or refresh.
When we create a scorecard, we can create a few goals for each row – how we stand in comparison to last year, how much did we sell in comparison to our forecast, how much did this department sell in comparison to a fellow department etc.
We can also base one target on the calculation of another target.
If we name a column in a table as: filter, key, index etc. then SharePoint won't prevent us from building a filter on it, but the filter won't work. Avoid using reserved words in your column names (a column called country_key on the other hand won't be a problem).
We can also define a filter on any other tabular data source like a SharePoint list, an Excel file etc.
Using a Time Intelligence filter will give us a calendar. Connecting a filter to a time dimension will give us a tree view instead.
If we deploy from PerformancePoint to a "regular" page, we'll delete whatever content there was on that page. That means that if I had a text filter on that page – it'll be deleted. Maintenance of a page which has Dashboard content and regular content can be problematic and we should consider adding PerformancePoint web part manually to the page along with other web parts.
More Posts
Next page »