Microsoft Announces SQL Server 2014

Microsoft today announced SQL Server 2014, designed with “cloud-first principles” and featuring built-in, in-memory OLTP and a focus on real-time, Big Data-style analytics. No specific realease date was provided in the announcement.

“Our Big Data strategy to unlock real-time insights continues with SQL Server 2014,” said Quentin Clark, corporate vice president with the Data Platform Group, in a blog post. “We are embracing the role of data–it dramatically changes how business happens. Real-time data integration, new and large data sets, data signals from outside LOB systems, evolving analytics techniques and more fluid visualization and collaboration experiences are significant components of that change.”

A key feature of SQL Server 2014 is the incorporation of in-memory, online transaction processing (OLTP) technology stemming from a project that has been in the works for several years, codenamed “Hekaton,” Clark said. Developed in conjunction with Microsoft Research, Hekaton greatly improves transaction processing speeds and reduces latency by virtue of working with in-memory data, as opposed to disk-based data.

Microsoft touted the benefits of the “conscious design choice” to build the Hekaton technology into SQL Server 2014, with no need for a separate data engine. “Other vendors are either introducing separate in-memory optimized caches or building a unification layer over a set of technologies and introducing it as a completely new product,” said Dave Campbell, Microsoft technical fellow, when Hekaton was announced as a coming component of SQL Server 2014 last November. “This adds complexity forcing customers to deploy and manage a completely new product or, worse yet, manage both a ‘memory-optimized’ product for the hot data and a storage-optimized’ product for the application data that is not cost-effective to reside primarily in memory,” Campbell said.

Clark picked up on that theme in today’s announcement. “For our customers, ‘in the box’ means they don’t need to buy specialized hardware or software and can migrate existing applications to benefit from performance gains,” he said.

Clark also emphasized the embrace of cloud computing, noting how SQL Server 2014 will work seamlessly with the cloud-based Windows Azure to reduce operating expenditures for mission-critical applications. “Simplified cloud backup, cloud disaster recovery and easy migration to Windows Azure Virtual Machines are empowering new, easy to use, out-of-the-box hybrid capabilities,” he said.

The Microsoft exec also noted SQL Server 2014 will include improvements to the AlwaysOn feature, supporting “new scenarios, scale of deployment and ease of adoption.”

VBA functions in MDX and DAX

It turns out that the Visual Basic for Applications library functions are a great way to extend the functionality of your MDX statements, allowing you to do some very handy stuff.

Analysis Services uses stored procedure technology to extend the number of built-in MDX functions. It automatically registers two libraries: the Visual Basic for Applications library and the Microsoft Excel worksheet library. Visual Basic for Applications library is registered as a CLR assembly and Excel library – as COM assembly. MDX expressions can use any of supported VBA and Excel functions, as if there were native MDX functions.

However, it comes with a cost of performance,  CLR assemblies are hosted in a separate application domain and in addition to marshaling from native to managed code and back, all the calls are also marshaled across application domains, therefore calling a VBA function is an expensive operation comparing to calling an MDX function.

Here is a list of all the VBA functions available in Microsoft Visual Basic for Applications Language Reference that are supported in MDX; also, the list includes a note when there is functional equivalence with the DAX language.

MDX last day of week with tuples

So, working on a project with the TFS cube, a business requirement needed to retrieve the Last Day of the Week.

Now, in the TFS cube, there is a Date Dimension with two hierarchies already created out of the box.

The problem started when I was trying to use the LastChild function to get the last day of the week from the [Year – Week – Date] hierarchy. This function expects a Member of the hierarchy, and I needed to pass a tuple (or a set of members).

After doing some research and ask a couple of senior colleagues, we found the answer:

GENERATE(
[Date].[Year - Week - Date Hierarchy].[Week]
, TAIL(
DESCENDANTS(
[Date].[Year - Week - Date Hierarchy].CURRENTMEMBER
),1))

By using the Generate function we work with each member of a set separately, and then the function  joins the resulting sets by union. The Tail function is similar to LastChild, but Tail returns a subset from the end of a set and can be used to return the last member of the dimension. The Descendants function is needed to return the set of descendants of a member at a specified level or distance to memory.

MDXLastWeek

Video

Differences between SQL and MDX

I like the way the presenter explains the difference between SQL and MDX.

Understanding these differences is crucial for any developer who is interested in learning how to query OLAP cubes with the MultiDimensional eXpressions (MDX) language.

I found these really useful links and videos to help us get started with the basics:

Analysis and application of Agile Methodologies in BI Projects

As a software developer, the purpose of this post will be to analyze how different methodologies can positively impact the development of a project; I will compare the similarities and differences between IT and BI projects to proceed with an approach to the Agile methodology and its impact to BI projects.

For Business Intelligence professionals, with or without experience, I believe that the BI methodology topic should be considered the backbone of their professional development. A lot of professionals adopt methodologies based on Kimball’s industry standards for designing, developing, and deploying data warehouses and business intelligence systems; Likewise, other professionals design their own ‘flavours’ based on those same standards.

The Business Intelligence methodology gives us a better certainty that our project will achieve the proposed solutions within a range of time determined from an IT point of view, but it is very important to understand that BI projects have indeed two perspectives: business and IT.

The use of methodologies can orient us to correctly define the business solutions and project goals, but it won’t guarantee the success of them because of direct and indirect variables that can affect the outcome. For example, selected BI tools, employee’s technical and business backgrounds, budget, etc.

Even though I think it’s not a rule, generally in both IT and BI projects, the methodology is somehow aligned with the technology chosen as solution, in case of BI, the platform can define essential points related to the technology itself, like better practices, process documentation, component’s definition, roles involved, etc. Now, if we think about the standards that should not be affected by technology, we can consider that the business requirements should all look very similar regardless of the methodology.

We know that there are plenty of methodologies for software development. Traditionally, these methodologies focus on process control by establishing phases, tasks, techniques and tools; Reason why they tend to be squared and straightforward and are usually guided through the documentation generated in each of the designed phases.

This approach is not suitable for projects where the system environment is constantly changing — meaning BI projects — or where the nature of the project forces you to reduce the development time but keeping the quality — meaning IT projects —. Reason why, methodologies like Agile emerge as a good option to fulfill that methodology gap.

Dilbert

The impact of applying Agile methodologies to BI projects can be huge if it’s well done, BI applications are challenging by nature; As mentioned above, their requirements constantly change, plus the inevitable need to add indirect variables such as the addition of stakeholders, incremental scope and depth, which by result will directly impact on the project’s complexity. Agile Software Development addresses the challenges described above right on the principles that defines it (http://www.agilemanifesto.org/principles.html). But its success lies on the methodology used to apply those principles.

Agile development methodology attempts to provide many opportunities to assess the direction of a project throughout the development lifecycle. This is achieved through regular cadences of work, known as sprints or iterations, at the end of which teams must present a shippable increment of work. Thus by focusing on the repetition of abbreviated work cycles as well as the functional product they yield, agile methodology could be described as “iterative” and “incremental.” In an agile paradigm, every aspect of development — requirements, design, etc. — is continually revisited throughout the lifecycle. When a team stops and re-evaluates the direction of a project every two weeks, there’s always time to steer it in another direction.

Source: http://agilemethodology.org

The advantages of using Agile methodologies in BI projects become very clear after understanding the definition and principles: by developing a prioritized product features or requirements (Product Backlog) and dividing them into small chunks (Sprint Backlogs) we can deliver working software during frequent meetings with stakeholders, which at the same time will give us the opportunity to verify releases or even re-prioritize requirements.

In essence, I can conclude that the Agile methodology helps companies to build the right product instead of committing to develop a piece of software that hasn’t even been written yet. Agile empowers teams to optimize their release as it’s developed.

Decalogue

John C Jay

Some insightful lessons from Wieden+Kennedy’s Executive Creative Director, John C Jay. Although focused from a design perspective, I feel that these lessons would apply just as well for anyone working within any industry.

1: Be authentic. The most powerful asset you have is your individuality, what makes you unique. It’s time to stop listening to others on what you should do.
2: Work harder than anyone else and you will always benefit from the effort.
3:
Get off the computer and connect with real people and culture. Life is visceral.
4:
 Constantly improve your craft. Make things with your hands. Innovation in thinking is not enough.
5: Travel as much as you can. It is a humbling and inspiring experience to learn just how much you don’t know.
6: Being original is still king, especially in this tech-driven, group-grope world.
7: Try not to work for stupid people or you’ll soon become one of them.
8: Instinct and intuition are all-powerful. Learn to trust them.
9: The Golden Rule actually works. Do good.
10:
If all else fails, No. 2 is the greatest competitive advantage of any career.

Aside

Manifesto

Manifesto

It has been a while since I wanted to start this blog project.

Since the world didn’t end in 2012 and I recently started my BI (Business Intelligence) career, I will use these means to keep track of and discuss topics related mainly to this area, but I will throw the random post about other interesting tech topics. As well, I will be writing about my experiences in BI development hoping other colleagues can benefit from them.

Thank you and here we go…