How are we doing THIS year versus the same period LAST year? 
This is a common need in Business Intelligence. In fact, many design specifications for reports make use of a comparison between a selected period (year, quarter, etc) up to a certain date (for example November 15th, 2016) and a summary of the same information for the previous year (i.e. up to November 15th, 2015). 
This post shows how to implement this in DeepSee.

50
0 8 1,225

What is %SQLRESTRICT

%SQLRESTRICT is a special %FILTER clause for use in MDX queries in InterSystems IRIS Business Intelligence. Since this function begins with %, it means this is a special MDX extension created by InterSystems. It allows users to insert an SQL statement that will be used to restrict the returned records in the MDX Result Set. This SQL statement must return a set of Source Record IDs to limit the results by. Please see the documentation for more information.

Why is this useful?

This is useful because there are often times users want to restrict the results in their MDX Result Set based on information that is not in their cubes. It may be the case that this information may not make sense to be in the cube. Other times this can be useful when there is a large set of values you want to restrict. As mentioned before, this is not a standard MDX function, it was created by InterSystems to handle cases were queries were not performing well or cases that were not easily solved by existing functions.

50
1 0 270

Hi, Community! 

This is the 3rd part of DeepSee Web story - Angular base UI for DeepSee Dashboards, see the beginning here.

By design, DSW provides an implementation for every widget in DeepSee library. But there are some extra features in DSW which make solutions built with DSW dashboards more functional.  This article describes it.

20
1 0 514

Hi, Community!

I’m sure you are using Developer Community analytics built with InterSystems Analytics technology DeepSee:

You can find DC analytics n InterSystems->Analytics menu.

DC Analytics shows interactive dashboards on key figures of DC entities: Posts, Comments, and Members. 

Since the last week, this analytics project is available for everyone with source code and data on DC Github!

20
1 1 332

Hello InterSystems Community,

We're excited to announce that we've completed our first Open Exchange submission for InterSystems platforms.

BridgeWorks VDM is an ad hoc reporting and graphical SQL query builder application that was designed for any user who needs access to their SQL projections in InterSystems Caché, InterSystems IRIS, InterSystems IRIS for Health databases as well as access to InterSystems DeepSee and InterSystems IRIS BI Cubes with minimal SQL scripting experience.  

VDM features:

20
1 0 213

The DeepSee Shell Best Practices Series - Reset your DeepSee cache in the DeepSee Shell

This method is provided as a convenience for developers for use on development or test systems. You should not use this method on production systems as it will have immediate effect on the performance of end user operations.

20
0 0 268

The DeepSee Shell best Practices Series - Execute an MDX query in the DeepSee Shell with/without  results cache

This cache is different from cache reset. Cache reset clears everything in the namespace but “cache off” only clears the cache in ^DeepSee.Cache.Results and ^DeepSee.Cache.Axis global node for the corresponding cube. The difference is quite smaller in the case as below, but in some cases it can be a big difference.

20
0 0 244

Preview Mode was added to InterSystems IRIS Business Intelligence to give designers a quick view of what their resulting Pivot Table will look like without needing to wait for the results to fully execute. This can be beneficial when designing pivot tables because if you are dragging and dropping elements to see how they look/work in your pivot table and seeing if they have the desired data. Since you are exploring and designing, you don't necessarily care about the results at the moment, but you would still like to see how your table looks with the changes you have made.

20
0 0 175

The DeepSee Shell Best Practices Series - Example of using the MDX subquery flag in the DeepSee Shell

We have different flags for DeepSee advisors to look into the execution details and the methods DeepSee is using to do calculations.

flag [flagname] [on|off] – Turn a diagnostic on or off.

flag cmbr on|off – Show current member processing.

flag compound on|off – Show rewrite of compound queries.

20
0 0 351

I want to have a generic PercentOfAll measure that can be used for any dimension of the cube. This PercentOfAll should act like Count, but instead of showing the number of rows in a cell, it will show the percentage  (100*number of rows for that member/all rows) .It should not matter what dimension is being viewed. Ex

There are a total of 100 rows in the source class.

11
0 1 161

BridgeWorks is pleased to announce a VDM, v9.1.0.1.  This release includes the following updates:

Updates

  • Historical Linking is now based off connection profile name
  • Saved Formatting is now based off connection profile name
  • Tables and Fields column headers no longer hide based on connection type

Bug Fixes

10
1 0 113

In DeepSee, having a customer account cube. The cube includes a time dimension AccountCreationDate.

How do you create a calculated measure to get the total number of accounts from the beginning to a certain date in abscissa ?

I tried many things including this:
AGGREGATE(%TIMERANGE([AccountCreationDate].[H1].[MonthYear].&[190001],[AccountCreationDate].[H1].[MonthYear].CURRENTMEMBER) ,[Measures].[%Count])

But I don't get the right result because the date in abscissa creates a filter on the first date.

10
0 2 726

Filtering of your pivot is accomplished by the Slicer Expression with either a Where or %Filter clause. A single measure is part of the Slicer Expression.

IRIS Analytics (DeepSee) IRIS Analytics (DeepSee)MDX
00
0 0 0

The row expression defines what dimensions and measures appear on the rows of your pivot table as well as how they appear. Sets and CROSSJOINs are used.

IRIS Analytics (DeepSee) IRIS Analytics (DeepSee)MDX
00
0 0 0

DeepSee MDX supports several statements to generate a pivot or listing. SELECT is the most used statement. DRILLFACTS and DRILLTHROUGH return detail row information from the fact table and source table respectively.

IRIS Analytics (DeepSee) IRIS Analytics (DeepSee)MDX
00
0 0 0

I need to create a KPI similar to the  DeepSee.Model.KPIs.DemoTrendLines kpi in Samples.  This kpi supports a scorecard widget with a trend line.  It shows patient counts by city with a trend line of the count of patients over a decade.  What I want is to show the trend line based on the average allergy score ([Measures].[Avg Test Score]).  A pivot table query that shows the data I would want to base the trend line on is:

00
0 3 236
Article
Community Manager · Oct 2, 2015
DeepSee MDX - Shell

The DeepSee MDX Shell allows execution of MDX statements in a terminal session. The WITH clause, as well as the CREATE and DROP MEMBER and CREATE and DROP SET statements are also demonstrated.

IRIS Analytics (DeepSee) IRIS Analytics (DeepSee)MDX
00
0 0 0

Hi!
I am working on a project and I am facing a weird problem. I have created an MDX using Analyzer. This MDX executes very fast. I am trying to automate its execution with %DeepSee.ResultSet and the query never returns:

Set tSC = oMDX.%PrepareMDX(tMDX)
Quit:$System.Status.IsError(tSC)

Set tSC = oMDX.%Execute()
Quit:$System.Status.IsError(tSC)

What could be causing %Execute() to take so long to run while Analyzer is responding fast?

00
0 2 209

I currently have an MDX query/calculated measure as per the below:

 
[DaysOnListRange].[H1].[DaysOnListRange].&[Days 91 - 180]+ [DaysOnListRange].[H1].[DaysOnListRange].&[Days 181 - 365]+
[DaysOnListRange].[H1].[DaysOnListRange].&[Days 365+]

however it currently doesn't sum when there is a blank in the data.
How can I amend this query to include empty cells/only sum cells with data?

I've tried using nonempty but it doesn't appear to work in this context

00
0 2 287

Relative functions in MDX allow you to move from a member of a level to its relatives. Relatives include, parent, child, cousing, ancestor

IRIS Analytics (DeepSee) IRIS Analytics (DeepSee)MDX
00
0 0 0