Microsoft BI has been criticized about its complexity and price because it requires SharePoint Server Enterprise for deploying Power Pivot models and dashboards. Microsoft introduced Office 365 (E3 or E4 plan required) and SharePoint Online (Plan 2 required) to mitigate such concerns and allow you to host Power Pivot and Power View in the cloud. Power BI is the next step in the Microsoft BI cloud strategy. As…
Read article
I installed SQL Server 2012 SP1 CU5 and I got greeted with the following error while opening Data Quality Services DqsInstaller.exe can be found by default in the C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn folder. However, running the dqsinstaller-upgrade didn't fix the issue. To fix it, I had to recompile the .NET assemblies by running dqsinstaller-UpgradeDlls. BTW, CU4 is still marked as in research by the DQS…
Read article
Besides data messiness (in direct proportion with the company size), another common data-related challenge that every organization nowadays is trying to tackle is how applicable self-service BI is to their business and to what extent it should be used. The tools are here and they are readily available. For example, given that Power Pivot is now natively integrated with Excel 2013, your power users will…
Read articleI'll be speaking at the SQL Server Innovators Guild group in Greenville, SC on September 3rd. If you happen to be in the area, please join us to learn best practices for implementing an enterprise BI solution. Please confirm attendance at https://ssig201309.eventbrite.com/
Read articleSemantics relates to discovering the meaning of the message behind the words. In the context of data and BI, semantics represents the user's perspective of data: how the end user views the data to derive knowledge from it. As a modeler, your job is to translate the machine-friendly database structures and terminology into a user-friendly semantic layer that describes the business problems to be solved…
Read article
Variance calculations are a common BI requirement but good reference material is lacking. How do we create them in DAX? Let's say you need to calculate the variance between this month sales and the previous month sales. DAX date functions require a Date table. They won't work if you don't have a separate Date table. They also require that you tell PowerPivot (or Tabular) about…
Read article
A couple of SharePoint-related questions from students taking my PowerPivot class that are worth sharing. Question: Can I import data from and auto-refresh an Excel file deployed to a SharePoint library in PowerPivot? Answer: Yes, if you use WebDAV and specify the UNC path to the file. WebDAV requires the Windows Server Desktop Experience feature to be installed and the WebClient service to be started:…
Read articleRelationships in analytical models (Multidimensional and Tabular) are both a blessing and a curse. Once defined by the modeler, relationships power "slicing and dicing" data. The analytical tool doesn't have to worry about and include the relationships in the query because they are defined in the model and the server "knows" how to use them to aggregate the data. On the other hand, as they…
Read articleA really impressive white paper/book with insights about Tabular performance and inner-workings. Kudos to the authors for producing this colossal resource. "Tabular models hosted in SQL Server 2012 Analysis Service provide a comparatively lightweight, easy to build and deploy solution for business intelligence. However, as you increase the data load, add more users, or run complex queries, you need to have a strategy for maintaining…
Read article
