Any functionality in Spotfire to aggregate and group automatically over the dimensions in the table when we remove a dimension from table

[Enkeled Kanaj]

Hi,

 

Based on previous experience on another BI tool where we defined dimensions and measurements when we removed one dimension from the table the other column would be collapsed or grouped by being aggregated automatically in the table. Here in spotfire this can be done with pivot where we pass dimensions and aggregate the measurements columns but is a little bit time-consuming.

 

Below I give an example where I remove the dimension hour and the other columns collapse being grouped and aggregated automatically.

 

Edited August 1 by Enkeled Kanaj

[David Boot-Olazabal]

Hi Enkeled,

In Spotfire, you can use the OVER function.
You can find more information on this OVER function in the help or in the 'Edit calculated column' window.

For a normal table visualization, you have to code the aggregation yourself, as it has no axes like a bar chart. Your formula would then be sum([4G Uplink Data Traffic Volume]) OVER ([ERBS id],[EUtranCell id],[Date]).

As far as I know, adding or removing columns doesn't have an effect on the automatic adjustment of the formula in a table visualization, since the OVER context is hardcoded. But it would do so if you have a bar chart for instance, using the Axis.X for instance.

Kind regards,

David

[Enkeled Kanaj]

Hi David,

Yes, I know it and it functions well on visualizations but in tables the value gets repeated over the rows. Only pivot can do it to group.

[Olivier Keugue Tadaa]

Hi Enkeked,

Since I understand your request, I'd mention that the other BI tools you are mentioning, build their visualization reports on top of semantic layers. Those semantic layers differentiate between "dimensions" and "measures". The measures are usually defined with an aggregation method (mandatory). And every time you change the design of your report (depending on the dimensions and measures selected), an ad-hoc query is generated on the fly with the appropriate aggregations, and sent to the underlying database.
In Spotfire, our approach is different. We don't aggregate data by design. We consider all data at its possible detailed level and provide an in-memory approach to get all the potential insight from your data (aggregation is a potential loss of valuable information)
To achieve what you describe, you need to use a cross table (and define the aggregations on the values axis) and use the in-database approach (keep data external) to obtain exactly what you have with those tools. Indeed, Spotfire provides high aggregations capabilities ( including the OVER function mentioned earlier) that can be automatically pushed down to the database to benefit from this design principle (ad-hoc query generation and execution)
If you are accessing data with an information link, we can also find a way to generate aggregated queries automatically.

Let me know if you would like to explore these alternative solutions.

 

[Enkeled Kanaj]

Hi @Olivier Keugue Tadaa

 

Yes I want to know these alternatives. In SAP BI this was done on a entity known as universe. Here I guess it can be done on IL.

Of course I have alternatives which are time-consuming but when doing reports if that is done automatically when I remove one dimension that would be appreciated.

 

Thank you!

[Olivier Keugue Tadaa]

Thanks Enkeled. 

Unlike universes on SAP BI which is a central metadata management layer designed as explained above (based on dimensions/aggregated measure approach) we provide several ways to access data 

1- information services (using information links)

2- Data connectors

3- Advanced Data Services

In the last two options, you can create your data source with the keep data external option to be in the position where your queries are generated on the fly

Secondly, you need to choose your visualization in such a way that only the necessary (dimensions) will be sent in the "group by" clause of the generated SQL. A Table Plot is not part of them. 

How are you accessing your data, what is your database type? Depending on your answer we might provide a better way to obtain something similar. But note that our approach is different from those traditional BI tools.

[Enkeled Kanaj]

HI @Olivier Keugue Tadaa

 

we use a database build from Ericsson. For instance from DB visualizer we used such syntax as below. Now I use Information Links here with edited sql. If there is a way to build with IL then thats ok. Otherwise I will do them with pivot.

 

Ericsson have some inbuild features functionality but as we know always demand from different situation needs always flexibility and new tasks.

 

SELECT ERBS,eutrancellfdd,Date_id,hour_id,

(Sum(pmErabRelAbnormalEnbAct)+Sum(pmErabRelAbnormalMmeAct)) "#LTE Drops",
Sum(pmCellHoExeAttLteIntraF) "#Att exe intra",
(Sum(pmCellHoExeAttLteIntraF)-Sum(pmCellHoExeSuccLteIntraF)) "#Fail exe intra",
Sum(pmCellHoExeAttLteInterF) "#Att exe inter",
(Sum(pmCellHoExeAttLteInterF)-Sum(pmCellHoExeSuccLteInterF)) "#Fail exe Inter"

FROM dc.DC_E_ERBS_EUTRANCELLFDD_raw

WHERE date_id BETWEEN today(*)-5 AND  today(*)

and eutrancellfdd like 'dwdwd' 

GROUP BY ERBS,eutrancellfdd,date_id,hour_id