• Web API CORS Adventures

    September 10, 2013 / No Comments »

    I've been doing some work with ASP.NET Web API and I'm setting a demo service in the cloud (more on this in a future post). The service could be potentially accessed by any user. For demo purposes, I wanted to show how jQuery script in a web page can invoke the service. Of course, this requires a cross-domain Javascript call. If you have experience with web services programming, you might recall that a few years ago this scenario was notoriously difficult to implement because the browsers would just drop the call. However, with the recent interest in cloud deployments, things are getting much easier although traps await you. I settled on ASP.NET Web API because of its invocation simplicity and gaining popularity. If you are new to Web API, check the Your First Web API tutorial. To execute safely, Javascript cross-domain calls need to adhere to the Cross-origin resource sharing...

  • Power BI (Part 1 – Getting Started)

    August 31, 2013 / No Comments »

    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 I explained in a previous blog and in the SQL Server Team blog, Power BI is essentially a cloud combo of all the self-service "power" tools (Power Pivot, Power View, Power Query, and Power Map) available on a subscription basis and running on Microsoft Azure, precluding SharePoint on-premises installation and licensing. Microsoft has started inviting participants subscribed to the Power BI for Office 365 Preview to test the prerelease version of Power BI. During the Worldwide Partner Conference, Amir Netz, a Technical Fellow at Microsoft,...

  • DQS Error after CU Upgrade

    August 29, 2013 / No Comments »

    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 team and CU5 is not even on the list.

  • Does Self-service BI Make Sense?

    August 29, 2013 / No Comments »

    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 find a way to get inspired even if you don't roll out a formal self-service BI strategy. When self-service BI make sense Having consulted with and trained many organizations ranging from small companies to Fortune 100 behemoths, I think that the following two scenarios appears as good reasons for self-service BI: Organizational BI is not an option. For example, a small company might not have resources to roll out an organizational BI solution, such as a management dashboard, and the data doesn't require extensive transformation....

  • Speaking at SQL Server Innovators Guild

    August 27, 2013 / No Comments »

    I'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/  

  • Semantic Layer Flavors

    August 26, 2013 / No Comments »

    Semantics 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 and centralizes business calculations. Different vendors have different ideology and implementations of semantic layers. Some, such as Oracle and MicroStrategy, implement the semantic layer as a thin, pass-through layer whose main goal is to centralize access to data sources, metadata and business calculations in a single place. A Fortune 100 company that had a major investment in Oracle engaged us to recommend a solution for complementing their OBIEE semantic layer with user-friendly reporting tool that would allow end users to create ad hoc transactional reports...

  • DAX Variance Calculations

    August 15, 2013 / No Comments »

    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 your Date table. So, as a first step select the Date table, go to the Design ribbon, and then click Mark as Date Table. PowerPivot will infer the column of the date data type (you must have such a column). Sort the Month column with custom sort. For example, if your Month column returns the month in MMM-YY format, sort that column by another integer column in the format YYYYMM. More on this in a moment. Define a new calculated measure using the following formula:...

  • Refreshing Excel File in PowerPivot from SharePoint

    August 10, 2013 / No Comments »

    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: Question: If major and minor (draft) versioning is enabled in the PowerPivot Gallery, is there a way to configure PowerPivot to create major versions only on refresh instead of minor versions? Answer: This is not supported natively by PowerPivot because the snapshot process is not configurable. A possible workaround is to configure a SharePoint workflow to promote minor versions to major versions. As a side note, you should be careful with maintaining version history because the SharePoint content database will quickly grow. If you haven't...

  • Analysis Services Relationship Limitations and Workarounds

    August 10, 2013 / No Comments »

    Relationships 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 stand, all analytical model flavors (Multidimensional, Tabular, and PowerPivot) have inherent limitations. The following table compares relationship limitations between Multidimensional and Tabular (PowerPivot). Multidimensional Tabular and PowerPivot Many-to-Many Supported Not supported Parent-child Supported Requires flattening the hierarchy Role-playing Supported Not supported Multi-grain Supported Not supported Fact-to-fact Not supported Supported but rarely useful   Let's discuss these limitations in more details. Many-to-Many The classic example of a many-to-many relationship is a joint bank account that is owned by multiple customers. Many-to-many relationships are natively supported in...

  • Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services

    August 5, 2013 / No Comments »

    A 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 and tuning performance. This paper describes strategies and specific techniques for getting the best performance from your tabular models, including processing and partitioning strategies, DAX query tuning, and server tuning for specific workloads."

Training

We offer onsite and online Business Intelligence classes! Contact us about in-person training for groups of five or more students.
For more information or to register click here!

Books

Learn Power BI at your own pace with our latest book Applied Microsoft Power BI. Targeting information workers, data analysts, pros, and developers, It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.

Syndication