Data Governor v3.5 has been released

Data Governor 3.5 is an exciting new release that has introduced seamless integration with SharePoint, MDS, direct input of SQL Command Execution and Role Based Security.  These new features include:

  •  The ability to import and export data directly to and from SharePoint lists and / or Master Data Services removing the need to export data to  disparate intermediate repositories, prior to processing.
  • SQL Command Execution, allows you to enter SQL statements directly into Data Governor as a executable process.  This development has now removed the reliance on SSIS / Stored procedures for simple SQL Commands.
  • The introduction of Role Based Security  means that business users can be given role specific, limited access to run specific jobs independent of IT.
  • Stay tuned for Version 4.0 which will include the automated capability to migrate your processes from development to production removing the need to manually re-create processes for each environment.

Leveraging the SQL Server platform, Perspective enables faster time to value for Business Intelligence projects.   Perspective also provides the governance and stewardship you need ensuring the information you use to make critical decisions is timely and reliable.

Come and check out the new website to learn more about our solutions or Contact Us  to arrange a demonstration.

Microsoft SQL Server 2014 released – What does it mean for BI?

Microsoft SQL Server 2014 was released for General Availability on April the 1st and brought with it a host of new features including integration with Azure, in-memory OLTP, security enhancements and improvements to AlwaysOn. (you can go here for more details)

The most compelling change for Business Intelligence workloads from SQL Server 2012 is the introduction of Updatable Columnstore indexes.

Columnstore indexes introduced in SQL Server 2012 increased throughput and improved common data warehouse query execution times significantly.    However, the read-only nature of the indexes proved to be a limitation.  This meant they had to be dropped and recreated when updating the underlying table causing additional processing overhead which proved difficult to accommodate near real-time BI.  Another drawback encountered was the extra space the columnstore index required as the indexes had to be stored in addition to the existing row-based tables as they were built on top of the unpinning tables.

In comes SQL Server 2014 with its updateable Columnstore indexes.  This development will change the way in which traditional SQL Server data warehouses have been implemented.  Immediate improvements include:

  • Columnstore indexes are created without reliance on row based storage which reduces space and processing requirements
  • As they are now updateable, there is no need to drop indexes and rebuild hence supporting near real-time data provisioning

In short, this is a key improvement to SQL Server 2012, which will change the way in which we think about data warehousing in the future.

You can download the Evaluation for SQL Server 2014:

On Premise

Cloud

 

 

Reece selects Kepion and BizData for Enterprise Planning System

Seattle, WA and Melbourne, Australia

BizData, a premier Microsoft Gold Partner specialising in Business Intelligence and Data Platform in Australia, and Kepion, a leading provider of Planning, Budgeting and Forecasting software, today announced that Reece has selected Kepion Planning as strategic partners to deliver an Enterprise-wide business planning solution.

Reece is Australia’s leading supplier of bathroom and plumbing products in Australia with over 450 branches across the country. The solution will enable over 550 staff to actively contribute to budgets and forecasts, providing real-time consolidation across the business.

“We have been looking for a cost-effective platform to evolve our budgeting and planning processes as part of our commitment to continuous improvement. We evaluated over several platforms, and found that Kepion was the best value for money and leveraged our existing Microsoft Business Intelligence investments.

BizData was a natural choice as an implementation partner based on their depth of experience with implementing these systems for Finance, Sales and Operations.” said Sasha Nikolic, Operations Support Leader at Reece.

“BizData is a highly-skilled and experienced provider of planning, budgeting and forecasting systems and have a great depth of expertise on the Microsoft platform.  We are pleased with the extraordinary momentum that has been building for Kepion planning to enterprise and mid-market companies in Australia.” said Kevin Hsu, VP of Business Development, Kepion Solutions.  “BizData’s reputation aligns with Kepion’s own commitment to product and service excellence.”
Kepion delivers enterprise-wide solutions based on Microsoft’s technology across the Americas, Australia, Europe and Asia.  Companies that have adopted Kepion for their global planning, budgeting and forecasting process include some of Microsoft’s top enterprise customers.  Kepion allows business users to easily enter their targets for company-wide strategic plans, top-down & bottom-up budgeting and continuous fiscal & rolling forecasting, all within a single, easy to navigate web interface environment.

About Kepion

Kepion delivers planning, budgeting and forecasting software for office of finance.  Kepion is built on the Microsoft BI platform powered by SQL Server.  Kepion provides an entirely web-based planning solution founded on the principle “Simply Flexible: Providing simple access & navigation to your needed information, combined with a fully ­flexible platform to build solutions that fit around your key business functions”.

For more information, please visit www.kepion.com

About BizData

BizData is an expert Business Intelligence services provider with a focus on enabling clients to leverage their data for better business decision making. BizData prides itself on delivering robust Business Intelligence solutions, having delivered over 200 projects to clients in the Financial Services, Manufacturing, Utilities, Health and Services industries, and provided solutions for Finance, HR, Operations, Sales, Marketing and IT departments. BizData is a leading Microsoft Implementation Partner and has been few companies in Australia to attain Gold level specialisations in Business Intelligence, Collaboration & Content and Data Platform. For more information, please visit www.bizdata.com.au

Please refer all enquiries to regarding this press release to:
Nadav Rayman, +61 419 568 226, nadav.rayman@bizdata.com.au

We welcome a new year, take a collective sigh of relief with the weather change, make sure we stick to our resolutions and look forward to another installment of:

SharePoint 2013 Cool Tricks for the Technically Challenged – SKY DRIVE PRO

Office 365 and /or on -premise installation of a SharePoint 2013 server has bundled in a new feature that has quite successfully confused many.  Sky Drive Pro.

Why the confusion?  Because Microsoft already has a product called SkyDrive.  They are not even related.  SkyDrive has nothing to do with SharePoint.

Most of us know SkyDrive (and its competitor DropBox) as a free personal storage facility available for Hotmail, Live or Outlook.com account holders.  We are all pretty familiar with how it works, you can store and share your documents, photos and files with anyone you like, you can let others view and edit your files, and best of all you get 7GB free storage. (you need to pay for more).

SkyDrive Pro is a document synchronisation service that lets you sync SharePoint Document Libraries with your own computer.

How?

A site collection in 0365 will display a very well thought out titled link displayed as “SkyDrive” (no, this is not  SkyDrive, its still SkyDrive Pro) and points to the Document Library in your My Site:1 This is your 25GB of storage space in the cloud, and comes with your organisation’s subscription to 0365.

(note: if you utilise SkyDrive Pro via an on-premise SP2013 installation, then your IT team will decide how much storage space you can have).

For it to work, it has to be installed on your computer (this will either happen automatically via an Office 2013 installation, or you can download a stand-alone SkyDrive Pro Installer).  Once this is done, you can start to synchronise your SharePoint 2013 libraries with your SkyDrive Pro client immediately.

Using it is quite simple.

On your PC

Create a folder in your SkyDrive @ <companyname> directory.  In the example below, I’ve created Client Assets: 2You will notice the little symbol on the folder indicating that its currently sync’ing.

Within seconds a green tick will appear, and you know that your new Client Assets folder is now sync’d with your online document library.

3

You are now ready to sync the files that you want!  You can work on a document locally, save in your Client Assets folder from your PC, and within seconds the same document is sync’d and ready to be accessed, edited and collaborated on from the cloud.

Note:  When you save a document you’ll notice an option to save to SkyDrive.  Of course this is NOT SkyDrive Pro, but simply SkyDrive.

4Your organisation’s folder (in this case “BizData”) is actually your SkyDrive Pro directory (well your personal directory but you’ll see what I mean with the screen shot below).  This is where I’m going to save this document.  You may see a series of folders, click on the one titled ‘Documents’ to see your SkyDrive Pro folders.

5

The folder that you saved your file in, will sync automatically, and once the green tick appears, your file is now online!

6

7

You can continue working on your document, hit save and your changes are captured and sync’d ready for use from any device.

Note: “Drag and Drop” also works :)

If you are online:

Click on your SkyDrive link and you will be presented with a familiar library view where you can immediately start adding new documents, or dragging and dropping files in.

8

You can also create documents directly from your SkyDrive Pro Library, and choose to share the file with anyone, all you need to do is type their email address (this must be exact) if external.  (note: sharing externally works provided the recipient’s corporate network security allows access to SkyDrive Pro.)   An automatic look-up is actioned for internal addresses.

In the example below I’ve created a PowerPoint Document by clicking on “+new document”

9By default the new document will open as a web app as you have created it online, but of course you have an option to open in an on premise version of PowerPoint, or even download a copy if you want to.

Once you have finished editing, simply hit ‘Exit’.  The document is already saved!

10You can also elect to share (you don’t have to of course).  Simply hit the ellipse at the end of your file name:

11

Shared files and folders are represented by a ‘shared’ (two people) icon.  A padlock represents files / folders that are only visible to you.  Documents in your SkyDrivePro library are private by default.

Sync Back:

Automatic sync’ing only occurs upstream.  Anything you create in your document library online you’ll have to manually sync the folder to your pc, you’ll see the “SYNC” icon in the top right hand side of your window:

 121314

….and ta da!

15

So, here are the basics:

  1. SkyDrive Pro IS NOT SkyDrive
  2. SkyDrive Pro is a sync’ing service not really a Document Library
  3. Automatic sync occurs upstream
  4. You can share or keep documents private
  5. You can sync any document library from SharePoint
  6. You can have all of your documents, spreadsheets, presentations etc available anytime, from anywhere and know that versions are sync’d!

#10 Tip: Report Alerting

Since the first release of SQL Server Reporting Services back in 2004, there has been the ability to automate the execution and delivery of reports with subscriptions. Commonly reports are delivered by email or to the file system, and there is an extensibility capability to allow developers to create custom delivery channels.

There are two types of subscriptions: standard and data driven. Standard subscriptions allows a user to configure and schedule a report delivery to themselves. Data driven subscriptions, as the name implies, retrieves a query result (based on any valid query), and each row in the query result is used to execute and deliver a report to, for example, a specific email address. A single data driven subscription could send reports to many recipients. Note that data driven subscriptions are only available in Business Intelligence and Enterprise editions of SQL Server.

Email subscriptions can be used to easily alert audiences with reports. A standard subscription will always run according to its configured schedule, and so is probably not a good choice to use as an alerting mechanism. The data driven subscription, however, can be configured to use a query that only returns rows when alert conditions are met. It is possible that a data driven subscription query will return zero rows and therefore will not execute or deliver any reports, and it is also possible to schedule the subscription to run at frequent intervals, perhaps every minute.

A new approach to drive alerts notification with Reporting Services was introduced in SQL Server 2012. The data alerts feature was designed to specifically address alert notifications by email. Providing Reporting Services is integrated with SharePoint, users can configure alerts that will automate notifications. The feature must be setup by an administrator, but is intended to be used by business users.

Working with report subscriptions and data alerts is covered in the Microsoft BI Academy “Zero to Microsoft Self-Service Business Intelligence course, written and presented by the author of this article.

#9 Tip Custom Report Layouts with the Excel CUBE Functions

​Seven CUBE functions were introduced with Excel 2007 to enable retrieving Analysis Services data model (including PowerPIvot data models) members, sets, and values. These functions play an important role in allowing a report author to create custom report layouts cell-by-cell, and to allow interactivity through user input and filter features, like slicers and the timeline (introduced in Excel 2013).

cube

An easy way to become familiar with the CUBE functions is to create a PivotTable based on an Analysis Services data model. The PivotTable can then be converted to formulas: Each cell of the PivotTable is converted into a formula that uses a CUBE function to retrieve a member or values.

In brief, the CUBEMEMBER function returns a single member, or tuple, from the data model. The CUBESET function retrieves a set based on any valid set expression, and the CUBESETCOUNT function allows determining the number of members in a CUBESET result. The CUBERANKEDMEMBER function retrieved a member from a CUBESET result. The CUBEVALUE function retrieves a value from the data model based on input filters. Cube members, sets, slicers and timelines can be used to filter the CUBEVALUE function. Finally, the CUBEKPIMEMBER function enables retrieving KPI metrics from a data model KPI, and the CUBEMEMBERPROPERTY function enables retrieving a member property for a member (supported on multidimensional data models only).

Generally, the CUBE functions work well for dashboard-style layouts (see image), especially when published to SharePoint or Office 365 for rendering in a browser. Their use would not be recommended for report layouts that could be generated with PivotTables, PivotCharts or Power View.

Working with CUBE functions is fully covered in the Microsoft BI Academy Self-Service BI course, written and presented by the author of this article.

Trick #2: Cool SharePoint 2013 Tricks for the Technically Challenged

​The SharePoint 2013 development team at Microsoft have put a fair amount of thought into what would make our lives easier when working with lists and libraries in SharePoint. How many of us have thought “wouldn’t it be great if we could just do some bulk edits to data in lists without having to open each one individually?” Well now we can.

The ‘Quick Edit’ function allows you to perform inline editing of list data. In a familar ‘datasheet’ view, you can add, delete and modify existing list data in rows and columns, similar to changing data in Excel. The coolest thing about this nifty bit of development is the ability to also copy and paste from other lists or documents or even from Excel, directly into the sheet.

‘Quick Edit’ is a setting you can enable or disable for each of your lists and libraries. To demonstrate how Quick Edit can be firstly found, and secondly turned on or off see the series of screen shots below.

For the purposes of this demonstration i’ve created a new list titled “Useful Sites” and included 2 links:

1

I’d like to add a few more items to this list. Traditionally I would select the ‘+ new item’ option which take a second to open and presents me with a new screen similar to this:

2

which is great. I can hit ‘Save’ and my new item appears in my list (Bing in this case).

3

But what happens if i want to add many more? The ‘one-by-one’ approach starts to get quite tedious.
Click on the ‘List’ tab in your top ribbon. Toward the right hand side you will find ‘List Settings’. Click on List Settings and look for ‘Advanced Settings’.

Click on ‘Advanced Settings’ and scroll down until you find the ‘Quick Edit’ function. Make sure the ‘yes’ option is marked if you want to keep Quick Edit on. This is the same location where you can also disable it of course!:

5

So, lets return to our data. Click back on the List tab in your ribbon and find your ‘Quick Edit’ button. Click and your list will now appear like this:

6
How cool is that? So i’m going to go ahead and enter 5 more items directly into this view (also copying and pasting URLs directly from page to cell):

7

Quick Edit also takes it a step further. If you right click your mouse on any given row you can delete your item directly from the list:

8

You can also Add additional columns with pre-set formats directly from the ‘+’ on the right hand side:

9

Once you have edited the data, simply hit the ‘Stop editing this list’ option and your changes are saved!.

There are a couple of points to remember with Quick Edit.

1. The Quick Edit option is available for your Document Libraries and Lists under ‘Advanced Settings’ if not already enabled.

2. The Quick Edit option may not be enabled if you have items grouped in a particular list or library view. To enable Quick Edit ensure that you have ungrouped your items first.

I hope you’ve found this trick useful! Stay tuned next month while we uncover what SkyDrive Pro is all about.

Trick #1: Cool SharePoint 2013 tricks for the Technically Challenged

Trick 1: Promoted Links

Welcome to the inaugural launch of ‘Cool SharePoint 2013 tricks for the Technically Challenged’.

SharePoint 2013 brings with it a number of goodies that appeals to non-tech folk like me. Building on a steady stream of end user centric development options since 2007, SP 2013 is giving us the capability to achieve slick, professional looking Intranets, Extranets and Document Management solutions with little more skill required than some extra reading and clicking of the mouse.

Given the depth and breadth of options out there, it’s a little difficult to know where to start. I posed the challenge to the BizData team, to come up with a list of cool tricks we wanted to share with you. There is only one simple rule: no ( ok sometimes minimum) technical skills required!

By now, we have all become used to seeing Microsoft’s ‘Metro’ design, first adopted with the Windows phone, and now incorporated throughout Microsoft’s suite of products, the ’tile’ has gained considerable popularity and SharePoint 2013 has given us a simple way of incorporating this design into our sites.

The Promoted Links App is a new feature with SharePoint 2013 and lets us store links that will show as tiles on pages where you choose to display them. Making it accessible across a range of devices, and providing a clean and modern feel, Promoted links allow you to surface common links to users, whilst giving you the creative freedom to associate nifty images with the links you want to promote.

The feature also allows you to configure a title and description for each link, which in turn becomes the sliding semi-transparent window that glides over your image. This is especially useful when screen real-estate is an issue, and pretty handy on a mobile device as the ‘click’ is a simple press on the tile.

So let’s start.

Oh, before we start there are some basic things you need to do that make the Promoted Links configuration a lot smoother.

Firstly, decide on what links you want to promote. Get your URLs ready and think about what you and your users are going to find the most useful.

Secondly, get your images ready. These can be traditional Metro tiles, or just content appropriate images that best represent the link you want to promote. Now, SharePoint 2013 out of the box defaults the tiles to 150 x 150. You can change them, but the solution is not an ‘on/off’ button but code. Click Here if you’re interested in changing the size, but given the aim of these posts, let’s just work with what comes OOTB.

Thirdly, make sure you save your images in SharePoint (In this example I’ve saved in the Style Library). Remember the location and the path because although Promoted Links is pretty cool, you can’t actually search to find your image location, you have to specify the path directly (I’ll show you where further down)

Part one: Setting up your links

1. Navigate to your SharePoint 2013 site. Click on tools icon and navigate to Site Contents.

picture1

2. Select ‘add an app’
3. You will notice an app titled ‘Promoted Links’. Select.

picture2

4. Give your Promoted Links list a useful name. For the sake of creativity I’ve titled this one ‘Promoted Links’ !

picture3
5. Because this list is new (empty) click on ‘All Promoted Links’ view to add tiles
6. Select ‘new item’
7. Fill in details as specified:

picture4

Think carefully about your Description as this will be the text that appears on the semi-transparent layer of your tile. Also don’t get too carried away with long sentences as there is a limit to the amount of text that can appear, and you don’t want it truncated when time comes to put on the page.

You can actually control the size of the font used on the title and the description that appears. It will require a little tiny bit of code, and I’ll cover it in an upcoming blog.

The rest is pretty straight forward. You can select where your link will open via the ‘Launch Behaviour’ option, and also dictate the order in which the link appears. Go on and hit save.

And here is what your promoted link will look like:

picture5

Part 2: Placing your promoted links on a page

Now that you have your Promoted links set you simply add them to your page via an App Part

1. Navigate to your page and click ‘Edit’
2. Select ‘Insert’ from the ribbon and select ‘App Part’
3. You will see your newly made Promoted Link listed:

picture7

4. Click ‘Add’ and your new link will appear.

You’re done! Well kind of. This is a web part like any other which means if you want to edit how the app part appears on the page including title, borders etc, you will need to edit via the standard edit options as part of your web part.

The final result (seen in a suite of 3 in this example) looks like this:

picture8

There are some limitations with using Promoted Links out of the box (limitations that can be overcome with some custom code, but we are keeping it simple here). If you want to:

1. Resize
2. Change spacing between links
3. Change font size
4. Wrap a number of tiles onto the next ‘line’

You will need to source (or write) code to do this. Point 4 is actually quite important. If you have a number of promoted links, they will appear in a horizontal line across the width specified by your page / app part. If the total width goes over the defined size, you will see a horizontal scroll. Not so cool. To avoid this you may have to create multiple Promoted Links lists and stack them when inserting the app part on your page.

Stay in tune for our next update where I walk you through a very useful function called ‘Quick Edit’.

#8 Tip of the Month Building Reporting Services Reports Based on Analysis Services Data Models

Analysis Services can deliver data models that allow business users to connect, query and report with relative ease. In SQL Server 2012, these data models can be designed by either using the multidimensional approach (cube) or the tabular approach. Regardless of design approach, the data model is queried by using Multidimensional Expressions (MDX) – except if the tabular data model is configured for DirectQuery mode.

There are many good reasons to deploy data models. In brief, they are deployed to deliver intuitive data access to business users, to accelerate access to potentially extremely large data volumes, to encapsulate business rules and key performance indicators (KPIs), and to deliver data and calculations as a “single version of truth”. The purpose of this article, then, is to describe how Reporting Services can be used to leverage and query data model data – and metadata – to build and deliver reports.

Reporting Services includes a number capabilities to connect to and query an Analysis Services data model. It include a data processing extension, a graphical MDX query designer, extended field properties and a special aggregation function, simply named Aggregate.

The graphical query designer is available in both Report Designer (available in SQL Server Data Tools, formerly known as Business Intelligence Development Studio) and Report Builder. Its purpose is to construct an MDX query that is suitable for Reporting Services to deliver as a dataset (which is by definition a collection of fields). As an MDX query can possibly return a multidimensional set, the designer constrains the query design to one which returns a two-dimensional result. Note that there are requirements that the query include at least one measure, and that all measures are placed on the columns axis.

The query designer has two modes: Design and Query. In Design mode, the report author uses drag-and-drop techniques to introduce measures, KPI metrics, and hierarchy levels into the query pane. This effectively constructs an MDX query under the covers. The query can be filtered by using members from hierarchies, and, with the simple check of a checkbox, the filter can be parameterized, which results in the automatic creation of a report parameter and a (hidden) dataset to retrieve the available values for the parameter. There is also the ability to define calculated members, and you will need an understanding of MDX to work with this capability.

In Query mode, the report author works directly by crafting the MDX query. It is often helpful to start in Design mode to produce a query close to your requirement, and then switch to Query mode to revise the query. Note that once the query is modified in Query mode you cannot revert back to Design mode.

As a result of producing the dataset query by using the query designer, a predefined set of cell properties and dimension properties are automatically added to the query. These are surfaced through extended field properties which can be used in report property expressions. There are 14 extended field properties beyond the commonly used Value property. Extended fields include UniqueName (the fully qualified member name), FormattedValue, and font formatting and colour properties. The latter are ideal for ensuring a report uses the formats, fonts and colors that the data model developer intended. In Report Designer (but not Report Builder), when constructing an expression in the Expression window, Intellisense will provide a list of properties for you to choose from. Note that most extended field properties are only relevant for multidimensional data models.

Tip 8 image

Lastly, to exploit the superior performance of the Analysis Services storage engine to compute aggregations (as opposed to Reporting Services retrieving and aggregating potential large volumes of granular data), the Aggregate aggregate function retrieves server aggregates. It does this by modifying the dataset query to include additional members that map to, for example, the row groupings in a table. This way, Reporting Services delegates the aggregation task back to Analysis Services, and this results in faster data processing, less network traffic, and accuracy (the report author should never assume how values in a data model are aggregated, as the data modeler can use techniques to override natural aggregation logic).

Reporting from Analysis Services data models by using Reporting Services is fully covered in the Microsoft BI Academy Reporting Services course, written and presented by Peter Myers

Power View for Multi-Dimensional

Good news for organisations that have wanted to use Power View, the powerful interactive data visualisation tool, against their Analysis Services cubes.

Released in May, the SQL Server 2012 Service Pack 1 Cumulative Update 4 provides support for Multi-Dimensional models as a source for Power View reports.

Another important new feature that is available to Power View (for both Tabular and Multi-Dimensional Models) are Pinned Filters, which can be used to filter across multiple views.

Read more about the release here and here.