-
Microsoft Azure Machine Learning
June 18, 2014 / No Comments »
Yesterday, Microsoft announced Microsoft Azure Machine Learning, previously known as project Passau. Your humble correspondent has been participating in the Preview Program. Basically, Microsoft Azure Machine Learning is a service for self-service cloud-based predictive analytics. You upload your data to the cloud, define datasets and workflows to create "experiments". Previously, you could create data mining models using the SQL Server data mining capabilities. Microsoft Azure Machine Learning to organizational DM models is what Power Pivot to Analysis Services is. Besides its cloud-based nature, Microsoft Azure Machine Learning offers: Data sources – Allow business users to upload source data as files. Workflows – Business users can drag and drop tasks to create workflows, such as to perform basic data transformation tasks, remove outliers, train, and score mining models. Users will familiar with SSIS will undoubtedly find workflows similar. Users familiar with SAS data mining will do the same. Scalability – You...
-
Optimizing Arbitrary Shaped Sets
June 17, 2014 / No Comments »
I'm working on optimizing a fairly large cube (2.5 TB) with some 25 billion rows in the fact table. The customer was complaining about long-running queries. Most queries would specify a time range that was passed to the query filter. For example, if the user wants to see the aggregated data from May 25th 20:00 to June 2nd 19:00, the query WHERE clause would like this: WHERE ( { [Date].[Date].[Date].&[20140527] * [Hour].[Hour].&[20] : [Hour].[Hour].&[23], [Date].[Date].[Date].&[20140528]:[Date].[Date].[Date].&[20140601] * [Hour].[Hour].&[0] : [Hour].[Hour].&[23], [Date].[Date].[Date].&[20140602] * [Hour].[Hour].&[0] : [Hour].[Hour].&[19] } … additional dimension filters here ) This an example of using arbitrary-shaped sets which Mosha wrote about here and Thomas Kejser here. "The resulting union set has interesting shape – it cannot be represented as pure crossjoin of attribute hierarchies. Such sets are nicknamed "arbitrary shaped sets" in Analysis Services, because they represent shapes of arbitrary complexity. Analysis Services in general doesn't like arbitrary shaped...
-
Partition Bug with Visual Studio
June 10, 2014 / No Comments »
There is a glaring bug with Multidimensional and Visual Studio 2012 and above. When attempting to add a new partition, the Partition Wizard finishes without error but no partition is added. Interestingly, no one has reported this bug more than two years after these Visual Studio editions have shipped. A couple of workarounds exist: Use SSDT or Visual Studio 2010 to create partitions. You can open your SSAS 2012 project in any Visual Studio edition starting with 2010. Add the partition either programmatically using AMO or by making changes directly to the *.partitions file. On the subject of partitions, note that MS has marked the following features deprecated: Remote partitions Remote linked measure groups Linked Dimensions Dimension writeback These features won't be terribly missed. I haven't seen remote partitions being used in real life except in BI certification exams. Linked measure groups also needs linked dimensions, but again, nobody in...
-
Presenting at SharePoint Saturday Atlanta 2014
June 3, 2014 / No Comments »
I'll be presenting "10 Ways to Maximize Your SharePoint BI Investment" at SharePoint Saturday Atlanta 2014 on June 21st. "Join me to learn how to take the most out of your on-prem or cloud SharePoint investment to promote organizational and self-service BI. We'll compare features of SharePoint editions and discuss options to visualize data from corporate databases and analytical models. Business users will learn how to share their self-service BI visualizations with teammates. We'll discuss how Office 365 and Power BI can help you implement a cloud-based environment for ease of sharing, collaboration, and information access." I hope you can make it. You can register here.
-
Atlanta MS BI Group Meeting on May 19th
May 18, 2014 / No Comments »
Join us for our next Atlanta MS BI Group meeting tomorrow, Monday, May 19th, to learn about Power Query. Spread the word and invite your coworkers but RSVP on our website so we can plan food accordingly. Main Presentation: Power Query Level: Intermediate Date: Monday, May 19th, 2014 Time 6:30 – 8:30 PM ET Place: South Terraces Building (Auditorium Room) 115 Perimeter Center Place Atlanta, GA 30346 Overview: So much data is stored on the web, yet merging it with our enterprise data can be a challenge. In this session we'll use Power Query to quickly harvest historical data about SQL Saturday from its site, combining event, session and speaker details with other publicly available data sets. We'll create custom functions with Power Query's M language to collect and transform the data from multiple web pages. We'll then move the data into Power Pivot and explore our results with...
-
Calculating Inception-to-Date Measures
May 15, 2014 / No Comments »
A requirement popped up for calculating an inception-to-date (ITD) measure. MDX CALCULATED MEMBER Aggregate(PeriodsToDate ([Date].[Calendar Hierarchy].[(All)]), [Measures].[Sales Amount]) In MDX, ITD is not much different than other time calculations, such as YTD or QTD. We only need to specify that the calculation will be performed over the top-most level of the Date hierarchy whose default name is ([Date].[Calendar Hierarchy].[(All)]. Of course, time calculations should be added to a shell dimension and a scope assignment can be used then in the cube script: Scope ( [Accounting Date].[Calendar Year].[Calendar Year].Members, [Accounting Date].[Date].Members , [Relative Date].[ITD] ); this = Aggregate ( {[Relative Date].[PTD]} * PeriodsToDate ([Date].[Calendar Hierarchy].[(All)]) ); End Scope; DAX MEASURE =CALCULATE(SUM('Reseller Sales'[Sales Amount]), FILTER(ALL('Date'), 'Date'[Date] < MAX('Date'[Date]))) In DAX, we pass a table consisting of the dates we need to aggregate over as a second argument of the CALCULATE function. We need to use the ALL function to ignore the row...
-
3 Techniques to Save BI Implementation Effort
May 11, 2014 / No Comments »
Everyone wants to press a button and have the entire BI system generated and ready to go. But things are not that simple. You know it and I know it. Nevertheless, BI automation tools are emerging with growing promises that propelled them to the Top 10 BI Trends according to the Information Management magazine. As a side note, it was interesting that the same article put Big Data in the No 1 spot despite that Gartner deemphasized the Big Data hype (based on my experience and polling attendees to our BI group meetings, many don't even know what Big Data is). While I don't dismiss the BI auto-generators can bring some value, such as impact analysis and native support of popular systems, such as ERP systems, there are also well known cautions, include vendor lock-in, a new toolset to learn, suboptimal performance, supporting the lowest feature denominator of targeted database,...
-
How to Test SSRS Data Alerts without Corporate Mail Server
May 10, 2014 / No Comments »
Reporting Services data alerts were introduced in SQL Server 2012 to allow business users to receive e-mail notifications based on rules they specify. Data alerts require SSRS to be configured in SharePoint integration mode. However, you'd probably run SharePoint on a VM for development and demo purposes and you might not have access to a functional mail server. The following steps allow you to configure your VM to test data alerts with a local SMTP server: Assuming your SharePoint VM is running Windows Server 2012, open Turn Windows Features On and Off and add the SMTP Server feature. This will install a local SMTP server. Open IIS Manager 6.0 and start the SMTP Virtual Server. Configure the SMTP server to allow relay. Because SSRS 2012 integrates natively with SharePoint, you can't use the report server configuration files to directly change the SSRS settings. Instead, you must use PowerShell as I...
-
Point-to-point Geo Adventures
May 5, 2014 / No Comments »
Scenario: You need to visualize point-to-point geospatial data, such as routes from an origin location to destinations. The map region in Reporting Services (Report Designer or Report Builder) supports point-to-point mapping but more than likely your users would prefer an interactive report. As they stand, neither Power View nor Power Map support point-to-point mapping. Further down on the limitation list, Power View doesn't support layers while Power Map does support layers but doesn't support filtering, e.g. to allow the end user to select a specific origin. Point-to-point mapping is a frequently requested feature which I hope Microsoft will implement in a not so distant future. Meanwhile, you need to resort to workarounds. Workaround 1: If all the user wants to see is how far the destinations are located relative to a specific origin, consider creating a Power View report with two maps. The first map would allow the user to...
-
SQL Server Events in Atlanta
April 22, 2014 / No Comments »
Next week will be SQL Server-intensive and your humble correspondent will be heavily involved: Monday, April 28th: Power BI presentation by Brian Jackson, Microsoft for Atlanta MS BI Group with Pyramic Analytics sponsoring the event. This presentation will cover new and compelling Power BI features including: the data manipulation of Power Query, Power BI Sites, the Data Steward Experience, natural language BI using Power Q&A, and mobile BI functionality. There will also be a technical discussion of the Power BI architecture as it relates to authentication, storage, data refresh and the concept of self-service information management. Friday, May 2nd: Three SQL Saturday precon sessions (Deep Dive into the Microsoft BI Semantic Model by Teo Lachev, SQL Performance Tuning & Optimization by Denny Cherry, and What the Hekaton!? A Whole New Way to Think About Data Mgmt by Kalen Delaney). Ping Stuart Ainsworth on Twitter at @codegumbo for $20 discount! Saturday,...
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.
