During one of my icCube demo meetings last year, one of the attendants asked if it is possible to migrate from Microsoft Analysis Services to icCube and what could be potential challenges. Well, what do you answer to such a question? … Honestly?

Having quite a bit of in-depth knowledge of both icCube and Analysis Services, I knew it could be done, but I never did it before myself. So, last Autumn, having a couple of days available for research I started the mini-project of migrating the Microsoft’s flag-ship demo model “AdventureWorks” to icCube.

So, can it be done? Can you migrate an Analysis Services model to icCube?

Yes, definitely. It was surprisingly simple. And as a bonus you will get all the additional benefits provided by the icCube platform too. With the most important benefit (for me and my clients):

  • With icCube you get an actively maintained and developed business intelligent platform. Something that Analysis Services really has missed over the last couple of years. And that is especially painful if you run into a critical/performance bug.
  • With icCube you get a complete all-in-one analytical software package, containing ETL, a builder tool, a server management tool, an admin tool and a dashboard editor & publisher. There is no need for additional (expensive) tools.

The result: AdventureWorks in icCube, accessed as Excel 2016 pivot
(check the connection).

Below a summary comparison points to note on the migration. If you want to learn more about the migration process itself, you can read my other blog post on this topic here.

Migrating AdventureWorks to icCube

There were no challenges for doing the migration, it’s just different ways of doing things. The following table gives a summary:

Table 1. AdventureWorks in Analysis Services vs icCube

Analysis ServicesicCube
set-up the
data source
to SQL Server
database
to SQL Server
database
same
tables & viewstables with
SQL expressions
use of viewssimilar
dimensionsallows NULL valuesdoes not allow
NULL values,
assign default value
similar
snowflakemulti-level
on a view
similar
discretization
bucket count
indexing by
range
matter of
taste
dimension usagereferencedregular with viewsimilar
measures same
calculated measuresscoped functionsusing MDX++
functions
similar
other calculations3 minor changes
in MDX
same

As you can derive from this table, you can conclude that there were no real issues in the migration. But, be your own judge. You can check out the result for yourself in icCube, using the migrated “AdventureWorks” model.

This is what you need to experience “AdventureWorks” in icCube:

  1. a license that contains the backup functionality (under big data features, click self-hosted first). You can ask support for help with the license.
  2. the backup schema for AdventureWorks, you can request this here.

And while you are at it, please do not forget to evaluate the dashboard editor. The “AdventureWorks” model provides a wealth of information to display in icCube’s interactive reports.

Here’s already a sample image of one, just to give you a taste of it.

Cheers,

Arthur

about the author

Arthur van den Berg, www.inside-vision.com

It’s a kind of magic … to transform loads of data into insight giving dashboard products that show your audience what step to take next, based on your data.

I work closely together with icCube being a Dutch reseller. I love their product that allows me and my clients to launch meaningful dashboard products and to embed analytical insights into their software.

Embedded Analytics truly means allowing for a seamless integration of icCube into other softwares or webpages, not only from a technical point of view, but also from a design point of view.

The possibility of adding your own HTML, CSS and JS codes allows the report builder to design dashboards that precisely fit with your very own corporate branding and expectations.

This article explains step-by-step how to create the following customized HTML widget based on an HTML-based report (download available on page bottom).

Custom widgets can in fact be saved as templates and reused for future reports in icCube. Let’s get started!

STEP 0 – Knowing your data & define what you would like to display

This first step seems trivial, but you don’t want to underestimate it. Make a prototype of the data you would like to show on your new widget.

For this example, we are using the default Sales schema available when you download icCube. As the image shown above, we would like to:

  • display the measure “Amount” by support product (“Silver”, “Gold”, “Platinum”) contained in the Dimension called “Products”,
  • Display the total of those 3 products,
  • and have the widget respond to a filter by “Country”.

STEP 1 – Defining your widget’s style

Use a styling tool such as CodePen, to build your very own widget’s design. This step is usually done by a designer to ensure a professional and brand-consistent look to your widget and report.

Find our code example here.

Once you’re happy with your widget’s look, you can implement it into icCube.

Go to icCube’s Reporting Editor, start a new report and create a new widget template:

Insert your HTML code on the “HTML data received” field:

STEP 2 – Defining your widget’s properties

Your widget will depend on certain properties, which are values coming from your cube. In our case, as previously mentioned on Step 0 and again shown below, the properties are: the measure “Amount” for the Silver, Gold and Platinum products, as well as the total of these 3.

Write the following JS code to create these 4 properties and add it to the “After Render” field of your Widget Template:

function(context, node, props, onRenderedCallback) {     $(node).find("span[idccube='amount']").html(''+props.amount());    $(node).find("span[idccube='silver']").html(''+props.silver());    $(node).find("span[idccube='gold']").html(''+props.gold());    $(node).find("span[idccube='platinum']").html(''+props.platinum()); }

This code simply binds variables placeholders from your HTML template.

For instance, whenever you insert the following HTML code in your HTML template:

<span idccube='amount'></span>

it will be replaced by the icCube-defined property ‘amount’, provided that you also insert the following JavaScript statement:

$(node).find("span[idccube='amount']").html(''+props.amount());

‘amount’ is simply the property ‘Value Reference’ field content.

Under the configuration wheel of the Properties, attribute the titles that will be displayed on your widget to each of the properties. For instance, we want to call the total sum “Total Support Amount”. As mentioned above, the “Value Reference (in Properties)” should correspond to the one defined on your JS code.

STEP 3 – Data definition

Now that we have created the properties, we need to have the data definition that will “populate” your widget.

We want to show sales for selected members (a subset) of the Products’ dimension, i.e. only Silver, Gold and Platinum products, as well as their total.

Please note that the widget has a limitation, each data retrieved should be defined as a measure, so you can’t simply have the single measure Amount on your query, you need to create an Amount measure for each of those specific products, including the (sub)total.

Therefore, create a new Amount measure per product, e.g. the “Silver Amount” measure is the tuple:

([Measures].[Amount],[Product].[Product].[Article].[Silver]), FORMAT_STRING="#'###"

STEP 4 – Link properties to its according data

Going back to the widget design, select the according measure to its respective property thanks to the drop down menu. As shown below, Under the “Silver” property, the value displayed is “Silver Amount” that was just created on the previous step.

Note that you have a Save button on the widget allowing you to reuse this Widget Template for future reports.

Your very own, newest widget template is now done!

STEP 5 (bonus) – Adding events

Widget Templates, as all other available widgets in icCube, can respond to actions. Let’s add a map widget that includes an event by Country updating your Widget Template accordingly.

To do so, create a new Geo Chart, add Amount on Measures and the dimension [Customers].[Geography].[Country] on rows, and create an event on row click as shown below.

Simply add this event as a filter on your Widget Template’s data definition, as well as on the header of your widget (see HTML code).

Now, once you click on a country, your new Widget Template will respond and update according to your country selection.

Find the report file with this full example here.

Other HTML example

Make sure to check this other article on how to create a different HTML box (containing an icon/image), this widget example is available live here.

Need members to have specific colors on all reports without the need to redefine them each and every time?

Check how to do so on this tutorial video that shows how to set up a member color property on icCube.

For more information on member properties, please check the following documentation link: https://www.iccube.com/support/documentation/mdx/Member%20Custom%20Property.php

CUSTOMER EXAMPLES

Arthur van den Berg from Thuros / Inside Vision, icCube’s exclusive Dutch reseller, has shared some practical customer examples on this feature.

“You might not expect it at first glance, but this functionality has so many great applications in dashboards. For example: you can attach specific colors to your G/L accounts enabling support for accounting methodologies like #Coloraccounting. You can link percentage ranges to certain colors and use these throughout all dashboards, hereby creating a consistent look-and-feel helping users to immediately understand what to look for. Here is an example for the last application:”

Find below two other examples, one for accounting purposes:

and another for a hotel management dashboard:

How about making some details of icCube’s Reporting Editor more personalized to your corporate branding?

A new feature that allows to change the Reporting application’s buttons color as well as the reports’ default theme and palette on icCube is now available.

This feature is especially useful for customers who are embedding icCube into their own product or solution (to those who allow their users to edit reports, of course) and want to make some details of the application closer to their corporate branding.

Learn how to in 3 simple steps.

  1. Go to Docs > Public > icCube.json and click on the magnifying glass on your right to edit the JSON file.
  2. Change the following settings according to your corporate/branding defaults:
    • The Reporting application’s buttons color, under “primary”
    • The default theme when creating a new report
    • The default color palette when creating a new report

    For this example we have used the following settings:

    {   "theme": {    "colors": {     "primary": "#8EC63F"    }  },  "defaultTheme": "Statos",  "defaultThemePalette": [   "#1A9641",   "#A6D96A",   "#FFFFBF",   "#FDAE61",   "#D7191C"    ]  }

  3. icCube’s Reporting with your layout defaults is ready to use. Therefore, not only your new theme and color palette are set as defaults:

    … but also, the Application’s buttons and on hover text:

    … as well as the buttons and lines on widgets’ editors:

Nice right? Enjoy!

We are usually looking at our dashboards on our computer screens, but sometimes we are on the go and want to quickly check your dashboard on your phone, tablet or even just need the report printed out to be able to take it during a meeting.

Anyway, icCube offers you the possibility to create a same dashboard with multiple layouts for your different devices and needs: Desktop, Tablet, Mobile, Printing and Printing Landscape. Here’s how to.

Creating a new Layout

First create your dashboard as usual, the default layout is Desktop. The screens icon on your left menu bar allows to add more layouts.

When creating a new layout, you can directly add all your widgets at once or not. N.B.: it could make sense not to add all widgets, in the case of filters for a printed report for instance.

Check out an example of a Tablet layout:

… and an example of a Printing layout:

Sometimes, adding all widgets at once and moving them around will not be well adapted to the layout you are creating, e.g. font sizes, column widths and row heights. You therefore need to change some of the widgets’ configurations for it to better fit the according layout.

Layout-specific widget settings

By default, when the widget created on the Desktop is used for another layout, the “use default layout” box is checked. Uncheck it to change the widget’s configuration or reclick it to go back to the same settings as used on the Desktop report.

The image below shows the configuration of a table on a Printing layout with layout-specific column widths and row heights.

User-Defined and Reset settings

A last feature which can be very helpful – and was implemented a couple months ago – is the User Defined and Reset buttons.

The first allows to show only user-defined settings, quite practical if you wish to quickly see exactly what settings you have changed, and the second to reset all configurations back to default.

On this video, you’ll learn how to create an icCube report with 5 features, from basic to advanced:

      1 – Table
      2 – Filter
      3 – Conditional coloring
      4 – Sunburst
      5 – Event (row click)
Bonus: The editor is in Chinese! In fact, icCube’s Reporting Editor is available in English, French, Russian and Chinese. Enjoy!

For a same report, you can have different layouts: Tablet, Mobile, Print or Print Landscape. You can customize the widgets (have different configurations across layouts) so they can be better fitted to each layout!