topic Re: .NET To read Table Cells returned from a Hypercube in Integration, Extension & APIs

.NET To read Table Cells returned from a Hypercube

Dan-Kenobi — Mon, 25 Feb 2019 16:48:57 GMT

Good day everyone,

I am trying to do something I'd consider simple and straight-forward and, yet, I'm failing over and over again. It seems to me I'm missing some basic understanding around Hyper Cubes and how Qlik manages and serves this kind of object.

The ask is simple: to print all the contents in a Table defined by a HyperCube. Below is my .NET/C# code with comments where the problem's been raised:

public int PrintTableFromHypercube(string[] dims, int max_rows) {
    try {
        // Defines the Hypercube objects 
        QE.HyperCubeDef hyper_cube = new QE.HyperCubeDef();
        List<HyperCubeDimensionDef> dim_defs = new List<HyperCubeDimensionDef>();
        List<HyperCubeMeasureDef> measure_defs = new List<HyperCubeMeasureDef>();

        dim_defs.Add(new HyperCubeDimensionDef { Def = new HyperCubeDimensionqDef { FieldDefs = dims } });
        measure_defs.Add(new HyperCubeMeasureDef { Def = new HyperCubeMeasureqDef { Def = "1" } });
                
        hyper_cube.Dimensions = dim_defs;
        hyper_cube.Measures = measure_defs;

        QE.GenericObjectProperties generic_prop = new QE.GenericObjectProperties();
        generic_prop.Info = new QE.NxInfo();
        generic_prop.Info.Type = "hypercube";
        generic_prop.Set<QE.HyperCubeDef>("qHyperCubeDef", hyper_cube);

        // Creates the Hypercube on the actual Application
        QE.GenericObject obj = QlikApp.CreateGenericSessionObject(generic_prop);

        var pager = new QE.NxPage { Top = 0, Left = 0, Width = dims.Length, Height = max_rows };

        var qlik_data = obj.GetHyperCubeData("/qHyperCubeDef", new[] { pager });
        var hyper_cube_pager = obj.GetHyperCubePager("/qHyperCubeDef");
        var current_page = hyper_cube_pager.GetFirstPage();
        var data_page = current_page.FirstOrDefault();

        // Keeps reading data while the page is not empty.
        while (data_page.Matrix.Count() > 0) {
            // Reads each row from the current page of data.
            foreach (var row in data_page.Matrix) {
                /****************************************************
                    Here's where the problem is: No matter how many fields or measures
                    I add, my "row" object will always return a single NxCell. So, it seems
                    like I cannot traverse those values to print on the screen.
                    *****************************************************/
                Debug.WriteLine($"Current row has '{row.Count}' elements");

                // Reads each field in the current row
                for (int i=0; i< dims.Length; i++) {
                    Debug.Write($"Field '{dims[i]}': '{row.ElementAt(i).Text}'\t");
                }
                Debug.Write("\n");
            }

            // Jumps to the next page of data from the pager
            data_page = hyper_cube_pager.GetNextPage().ToArray()[0];
        }
        return 0;
    } catch {
        return ~1;
    }
}

Thanks in advance for your help

Re: .NET To read Table Cells returned from a Hypercube

Øystein_Kolsrud — Wed, 27 Feb 2019 07:26:30 GMT

That's a lot of code, and I haven't reviewed it in detail, but I see two things in that code that might not be what you want:

You create just a single hypercube dimension based on your input field. Such dimensions (with multiple fields) are typically used for stacked or cyclic dimensions. I get the feeling this is probably not what you want. I would assume that you probably want to do something like this instead:
```
var dim_defs = dims.Select(dim => new HyperCubeDimensionDef { Def = new HyperCubeDimensionqDef { FieldDefs = new[] { dim } } }).ToList();
```
You use "Width = dims.Length" when you define your start page. That will mean that you will never consider the number of measures you add when you construct your page. I would recommend that you use the "NumberOfColumns" property from the pager object instead, like this:
```
var hyper_cube_pager = obj.GetHyperCubePager("/qHyperCubeDef");
var startPage = new NxPage { Top = 0, Left = 0, Width = hyper_cube_pager.NumberOfColumns, Height = max_rows };
var data_page = hyper_cube_pager.GetData(new []{startPage});;
```

And finally I would recommend that you use the IteratePages method when you want to traverse the data like you are doing. With that construct you could write your code like this:

foreach (var row in hyper_cube_pager.IteratePages(new[] { startPage }, Pager.Next).SelectMany(pages => pages.First().Matrix))
{
	Debug.WriteLine($"Current row has '{row.Count}' elements");
	...
}

And two references that might be of interest to you:

On retrieveing data with the .Net SDK:
https://help.qlik.com/en-US/sense-developer/February2019/Subsystems/NetSDKAPI/Content/Sense_NetSDKAPI/HowTos/Net-Sdk-How-To-Retrieving-Data.htm
On use of hypercubes in the .Net SDK:
https://github.com/kolsrud/qlik-dot-net-sdk-hypercube-usage

Re: .NET To read Table Cells returned from a Hypercube

Dan-Kenobi — Thu, 28 Feb 2019 13:24:43 GMT

Hi @Øystein_Kolsrud , thank you so very much for your reply.

In short, yes it did work. My fundamental understanding flaw was around the HyperCubeDimensionDef where I did one single dimension with multiple fields instead of adding multiple objects. So, THANK YOU for that.

The second link you forwarded, for the GitHub project, has been really useful. So additional points for that.

One thing I need to comment is around the IteratePages method. For medium sets of data (50K+) and plus, it was taking a lot of time to initialize. I'm guessing the LINQ code asked the server to do the paging before returning the first on. So I fell back to my original approach where I get each page sequentially.

This was a great lesson and I really appreciate your help

Re: .NET To read Table Cells returned from a Hypercube

Øystein_Kolsrud — Fri, 01 Mar 2019 08:01:04 GMT

Good! I'm glad you were able to make progress!

I'm curious about that IteratePages behavior you saw though. Which .Net SDK version are you using? I know there was a bug in the implementation of that one a couple of releases ago that accidentally made it strict. But it's supposed to be lazy in the latest release.

Re: .NET To read Table Cells returned from a Hypercube

Dan-Kenobi — Thu, 28 Feb 2019 21:24:41 GMT

@Øystein_Kolsrud - I am using QlikSense.NetSDK, version 13.3.1 from nuget.

I just saw the most recent one is 13.9.0, but I won't be able to update this before the next release. When I can, I will update and give the IteratePages another shot.

Thanks again for your help. I see more posts like this rising in the future and I'll start tagging you if that's ok.

Re: .NET To read Table Cells returned from a Hypercube

Øystein_Kolsrud — Fri, 01 Mar 2019 08:00:03 GMT

OK, that would explain it. It seems the first version where that IteratePages behavior was fixes was in 13.8.0.

Funny you should run into that issue right now. I was in Krakow for the Lambda Days conference just last week and gave a presentation where I actually mentioned that very bug in the context of leveraging functional design principles in an object oriented environment.

Re: .NET To read Table Cells returned from a Hypercube

Dan-Kenobi — Fri, 01 Mar 2019 11:14:02 GMT

Do you happen to have a link for that presentation?

Re: .NET To read Table Cells returned from a Hypercube

Øystein_Kolsrud — Fri, 01 Mar 2019 18:14:13 GMT

It was this one:

http://www.lambdadays.org/lambdadays2019/oystein-kolsrud

They are currently in the process of uploading the presentations, but I'm not sure if mine will make it. There were some technical issues with the recording equipment when I did my talk, and I'm not sure if they were able to fix it in the end.

Re: .NET To read Table Cells returned from a Hypercube

Øystein_Kolsrud — Fri, 15 Mar 2019 07:21:13 GMT

Nice! It did make it! You'll find it here if you are interested:

https://youtu.be/Wq32ZjEfZCI

The part relating to the bug you saw starts here:

https://youtu.be/Wq32ZjEfZCI?t=1366

With the Qlik Sense specific part here:

https://youtu.be/Wq32ZjEfZCI?t=1614