(disclaimer: this transcript has been automatically transcribed so it may contain errors)

Hi, my name is Harvey Withington from the Cubewise CODE Team and I’m coming at you from Melbourne, Australia. Welcome to this presentation, building the next generation of forms, active forms with Slice. And it’s my great pleasure to introduce Louis Rincon, Louis boasts over 15 years experience in the TM1 world, showing an unwavering commitment to helping businesses conquer their financial and operational hurdles.

From his early days as an end user to later roles in consulting, in-house and currently in the Cubewise CODE team. He now brings you a new and powerful way to build Excel reports with IBM Planning Analytics. I love Slice. So this is a pretty exciting one for me too. If you have questions, please pop them in the chat or the q and a.

I’ll answer what I can and Louis can handle all the hard ones at the end. And without further ado, I’ll hand over to Louis to boggle your mind with the possibilities afforded by Slice. Thank you Harvey. Thanks for that nice intro. My name is Louis Rincon and I’m coming to you from beautiful Mexico. So today I’m going to talk about building the next generation of active forms with Slice.

Okay, so let me just share my screen and here we go. So what is the Slice? For those who don’t know, Slice is an Excel add in for Planning Analytics. It’s super easy to install, is fast, clearly connects directly to TM1 through the REST API. It has full support for hierarchies all the way through and also is backwards compatible so you can actually run your Perspective reports as well with it.

Okay. It’s a nice and simple toolbar as you can see on the screen, quite intuitive. So there’s nothing, it’s not rocket science really to use it. So what is it that we’re going to cover today? Just briefly, we’re going to warm up. Okay, let me just try that again and all right, if you give me the, okay, that’ll be good. All good. That looks way better. Thanks. Nice. Awesome. So as I was saying, if you can see, Slice is quite an easy, easy toolbar to use and what we’re going to cover today, the best of our abilities will be a quick warmup with Slice. Just some handy tips for the cube viewer and also how to apply view styles.

I’m going to tell you short very shortly what is an MDX form in case you’re not familiar with the concepts, it’s components. Also we’re going to use the MDX lab, which is a tool part of the Slice Explorer. And whilst we are going to be creating MDX forms, we’re going to use hierarchies as well, which is a very powerful thing. Also, we’re going to create on the fly calculations using calculated members in MDX and as a bonus I thought about displaying asynchronous views in Excel.

So, okay, we, we have that done. For those who don’t know what an asynchronous views is, it’s essentially selecting for each column the right combination of elements instead of stacking dimensions or hierarchies. You can see what I mean when I get to that part of the the demo. So what is an MDX form? So it’s a brand new way to build Excel reports with Planning Analytics, right? It’s driven by one MDX query. So you can combine that with the power of Excel. So essentially the limit is your imagination to be honest, fast, definitely fast and very flexible. Probably more flexible than traditional active forms. And the beauty of it as well, you can stack dimensions or or hierarchies on other rows or columns, which obviously makes it very powerful.

How do they work? Well one simple Excel function, there’s nothing more to it and only just one MDX statement. So that makes the report very flexible and very useful not to, not to mention also that the maintenance becomes very simple ’cause you only have one MDX query to look after, right? Obviously, as I said before, expand collapse columns.

We can create calculated members, we’re going to do something very simple so you get the idea of what I mean by that. And also you can have more than one MDX form in the same report or in the same sheet. So for all those active form hackers, this is a bit of a breeding space for you. Okay. So that one single Excel function has only six arguments, very simple arguments, the ID which is automatically assigned. You can also amend it to the report naming convention that you wish or that you have at your work. The server of course, or TM1 instance name the MDX query itself, it has a write back flag, but obviously Rules at TM1 security take precedence. You can also display different attributes, not only necessarily aliases, so it could be any say for example string attribute for that matter. And last but not least, we have the format range, which is very similar to what you used to with active forms. Okay, so with no further adieu, let’s go back into Slice. So here is my Slice ribbon on the top. I’m going to click on my Slice explorer so the window is open and I’m going to log in into my TM1 Server.

You will notice I wasn’t actually prompt for a password or anything like that. So Slice retains your credentials. So I find that quite actually handy and easy to do. And you have the sort of used to the same navigation applications, dimension cubes, processes, chores. There’s a bunch of really cool tools around administration that I won’t go through in this session, but definitely take a look at that. Also, some tools, in this case we’re going to be looking at the MDX lab just to test our queries. And so let’s get onto it. So I’m going to open the general cube. The GL cube is just like a standard GL cube. Everyone is familiar with this. I believe. You have the different dimensions with different color coding and some of the quick wins with Slice or what we call the hidden gems are actually in the context menu. Say for example, if I right click here and this net income and I have a take my context menu, essentially I can swap rows or columns, I can edit the subset where I can keep a particular element with its descendants or children’s and so on and so forth. So let’s just see this breakdown of this net income by just clicking into it.

So this is a quick way of accessing the list of elements that make up that particular consolidated element without having to go into the subset editor. Something else that you can do, if you go, say for example, on the dimension that I have on the columns, which is my measure dimension, I can actually swap by other dimensions that I have available.

So let’s say for example, I want to see this net income breakdown across different regions, right? So again, just one single click and you’re there, right? Maybe I want to see it by period and so on. So it’s quite easy, easy to do. Something else that I quite like. Let’s say if we pick the element Finland and you can see I can actually enter data here. Let’s say I do 20,000, it recalculates my view, but for whatever reason I made a mistake, I can always go into this little button that I quite love. It’s called on undo data entry. Hit that button and it’s going to restore the value that was originally in that sale. So that’s very handy in case you are entering budgets and so on.

Something else that we can do is create custom calculations. For example, let’s say let’s pick cost of sales and maybe net income, right click come here into custom rollup and let’s call this calculation percentage of net income. And we are going say is a custom calculation, let’s pick the cost of sales and just something simple, let’s divide it by then the net income and I want to replace my lease.

So essentially I just want to have a look at what I’m calculating. There you go. So I have my percentage with a red marker indicating that is a custom rollup and below I can expand that and see what are the two other rows that I was using to derive that calculation. So that’s just simple, quick wins in the view of Slice.

Something else that you can do is in the gear icon you can collapse, expand different things that, for example I want to see in my hierarchy panel as I said this comes is hierarchy aware throughout and you can collapse them as well. And last but not least, we also can apply different view style editors with different color coding and so on. I’m going to pick the horizon one that we designed for this conference and you can actually go and edit it, right? It’s quite simple to amend it. All you need to do is click in the area that you want to amend. You can edit the color code, you can change the text color, you can pick a different font. Also the font size and so on. So it really all you need to do is click and keep editing your view as you wish for. Save it or create a new one and then save it and you’re ready to go, right? Okay, so I’m going close this view and now I’m going to go into a different cube. This cube called Listing Analysis solution. It’s a simple cube, two dimensions, the first dimension, which is my listing. This is a cube that is holding listings from Airbnb or any other rental platform that you can think of. And I have different hierarchies for this listing.

So I have my host, I have a neighborhood and I have my room type. So we’regoing be using these three hierarchies just to demonstrate what you can do with MDX Forms. Okay, so if I open my cube view and then again I expand my hierarchy panel, I can see the hierarchies I was telling you about. Let’s apply the horizon view style and the first thing I’m going to do is right click go into export to MDX form.

So let’s export that into my book one click okay, and here I am, right? So next to the remove TM1 functions, there’s a little button which is a total to show the format area that I just want you to see that is quite similar to active forms. Let me just expand this a little bit more. There you go. And here is my first MDX.

So this is automatically generated by a Slice if I was to copy that MDX for example, and head back to my Slice explorer and open my tools. And I’m going to open my MDX lab and paste that query there. There’s a little icon that is called format code. Just need a click there and it formats my code so it’s easier to read, right?

If I run that query, you will see exactly the same as I was looking in my view. And as you can actually see in my Excel report. So next thing I’m going to do, I’m going to fast forward due to some time constraints that we have for this session. And I’m going to show you what I’m going to do. So I have my three hierarchies that I mentioned to you just now and I created a validation list.

So this is just a simple Excel validation list, nothing fancy about it. And we are going to be choosing, say for example, I want to see my neighborhood breakdown in this report. So if I go and say rebuild. There you go. So now I have a breakdown by neighborhood across my columns. I have my price and my average price. So let’s just quickly take a look at that MDX query in my MDX lab.

So it actually makes more sense also to you. All I need to do is just remove these double quotes that are coming from Excel. And as you can see on my columns, I have my price and I have my average price. And on the rows I’m just requesting the neighborhood hierarchy by its children and that’s it. So if I run that query,

I’ll have exactly the same thing, right? So heading back to my MDX form. So all I’m doing here is just playing with Excel by replacing the hierarchy name by the dropdown. And there you go, right? Within a matter of a minute you have a full dynamic MDX form report which allows you to swap from different hierarchies and you don’t have to worry about resizing cells or anything like that.

Okay? So let’s move on to the next challenge. So let’s add more MDX statements here that we can switch in between, right? So as you can see at the very top, I have six rows. I have one MDX for my host, one MDX for my neighborhood, one MDX for my room type, which is basically what I’ve done in the previous report that I was showing you.

And now I want to start nesting or stacking dimensions across rows and columns, right? So let’s copy this MDX and I’m going into my MDX lab first just to test it and see if it works okay. So once again, let me delete that paste that remove double quotes and I’ll explain this very briefly for those who are not very familiar with MDX.

So I’m using a subset called default, which is essentially a subset that holds all the measures I have in my measure dimension. I’m going to place that on the columns and then on the rows I’m going to stack first the listing hierarchy, the room type broken down by its children. And I’m going to stack then a second hierarchy called the neighborhood hierarchy. But instead of using the function children,

I’m going to use a simple TM1 subset that I just handpicked a couple of suburbs, which I call my whereabouts. If I actually run that query, we will see that I have the room type, neighborhood. And across all my columns I have all my measures. So let’s go back to Excel and let’s pick this query. Okay, I don’t what this is.

There you go. All right, let’s reveal this sheet and now we have it. So once again, all the formatting comes across, right? I have my listings in one column and then I have also my suburbs on the other column. So what if we actually want to swap the neighborhood on the columns stacked on top of my measures and leave the listings where they are on the rows?

So again, I don’t probably need a need to see that again, all I’m going to do is just run it straight in my report, I’m won, go to my MDX lab, run that and there you go. Right? So once again, simple and easy way to start shifting your report from rows and columns without major effort in terms of hacking or amending or working out different ways to achieve these, this sort of performance, these sort of behavior. Last but not least, these top listings, top 10, that’s something very common in OLAP technologies. So, but again, for those who are not familiar, it is always good to go to the lab and test your query before you actually actually run the report, right? So again, on my columns I have two measures, price measure and average price. And on the row I have my top count based on my average price, which is right here. Okay, so let’s run this query and there you go. So you will notice that the listing, it has only a code. So its ID and then I have my price and then my average price.

So let’s see how that actually runs in Excel and let’s say top 10 listings rebuild, and there you go. Now why do I see the descriptions here? Very simple is because in my NDX form there is a parameter called attributes and which is my cell B 17 and that cell is exactly this cell. So I’m requesting to TM1 to give me the actual description and not the code.

So that’s how simple you can implement the display of different attributes, right? Okay, so let’s go to fast forward again and now we’re going to use calculated members. So those are members that obviously don’t live within your measures dimension. You probably want to derrive a calculation. So I’m going to copy this query again back into my MDX lab just to test it and say paste.

And let’s see if, sorry, the code formatter has to help me out here. There you go. So the first thing I’m doing is defining the member called minimum revenue. So that doesn’t actually exist in my measure dimension and I’m using two measures to derive that. One is the average price and I’m multiplying that by the minimum available nights for that particular listing.

I’m going to place that on my columns right here. And on the rows I’m going to do a top 10, a top count. So I’m combining my previous top 10 query and I’m adding an extra calculated member. So if I actually run that query, you will see that the calculated member is now with a red marker, right? So let’s go back to there and let’s say minimum revenue and let’s review that sheet and here it is, right? So I have exactly that. So what would happen if I want to insert another MDX form in there, all I need to do is right click, go to Slice, insert form MDX form, let’s just pick the same cube for the purpose of demonstrating and the default view hit. Okay, now have a second MDX form. You will notice that it has a different formatting that’s essentially because its that’s the default. But all I need to do to keep the same format is take this to the top, hit enter. I don’t need this anymore. So I can actually delete that and I can rebuild this sheet, right? So once again it’s all formatted if I wanted to have a different MDX. So all I need to do is maybe reference the top 10 for example, hit enter. I don’t need this either. So we can delete that and review that sheet. And there you go. Right? So this is how simple you can start building two, three or four more active forms in the same report. And last but not least, let’s see what asynchronous views are, right? So I’m going to click copy and go into my MDX form, sorry, into my MDX lab and have a look at what asynchronous views are look like. So I’m going to run that basically I’m selecting a specified combination of columns by my account on the roles, right? This is how it actually looks here. And if I go to my MDX query and I say reveal sheet, well there you go. And obviously I hacked something similar that I’ve done before.

So instead of Northern Ireland, maybe I want to see Wales.Rrebuild that and I can actually start entering data if I may wish to do so. Okay, so that’s as best as I could do asynchronous views, but I want to just briefly show you that there’s some resources that you can look into it. So first of all, download Slice, it’s for free, try it, play with it. It’s very intuitive, it’s super easy to use. And if you want to start exploring a little bit more, we have this blog called Get the Most out of the MDX Forms with Slice. Also we have a blog that talks about how to create asynchronous views and how you can use them either on the slides or ARC viewer for that matter as well as on Excel.

Last but not least, have a look at our features, follow us on LinkedIn and if there are any further questions after the session, you can also feel free to drop an email this in. Thanks for that Louis. That was excellent and excellent timing. We’ve got six minutes left so if anyone’s got any questions you can pop ’em in the chat or the q and a section.

We didn’t build any up as we went, so our colleague Andres, I’m here answering questions already, so he got most of them. Oh nice. It’d be pretty hard to find a question that we knew the answer to that Andre wouldn’t. So yeah, one of the most important things I always recommend to people is just to give it a go, right? Like try Slice, really simplify a lot of things in terms of usability, performance wise, we putting a lot of enhancements in the latest release. So it’s quite fast in terms of how it can render your data. So give it a chance and you won’t be disappointed, I can assure you that. So can you tell people how much they can try Slice with very little risk? Because I think a lot of people when they, when they go to try Slice them, question that was asked was, can you have both Slice and Perspectives that is active at the same time? And the answer is that you cannot, but you can keep them both installed and just disable one. So it’s not too invasive exactly. But we have actually designed the experience to be very uninvasive. them, question that was asked was, can you have both Slice and Perspectives that is active at the same time? And the answer is that you cannot, but you can keep them both installed and just disable one. So it’s not too invasive exactly. But we have actually designed the experience to be very uninvasive.

So can you just explain a bit about how you would trial it without completely uninstalling TM1 or anything on your machine? Yeah, well it’s, it Slice as, as, as Harvey said, it comes with an executable file that you can run locally in your machine. So all you need to do is install Slice disable the Perspectives add-in and let Slice take you through the wonders an executable file that you can run locally in your machine. So all you need to do is install Slice Sable, the perspectives adding and let Slice take you through the wonders of TM1 to get the feeling of that look and feel. There’s no risk whatsoever that you are exposing you exposing yourself at all. And if you want to revert back to Perspectives for whatever reason you may have, you can always disable the Slice add-in or not start it at all, ’cause as I said, it’s installed locally and your Perspectives add-in will run as usual.

Right. Kevin asked how hard is it to switch between the two? Sorry, say that again? Harvey, Kevin Fry asked how hard is it to switch between the two? No, It’s not really hard. It’s just a, a matter of couple of clicks here and there and then you’ll be ready to go.

But the most important thing is not about the the switching, it’s just about experiencing the what Slice can give you. Because as Ben rightly mentioned at the beginning of Horizon, Perspectives eventually is going to go, right. So it’s a good way, it’s a good time to start familiarizing yourself with the tools available out there. And can you talk about backward compatibility a little bit?

So if, if I had a Perspectives report in Slice does it work and will it change the report or anything? No, well look, it’s backwards compatible. As I mentioned in my presentation, the only thing that you won’t be able to do is if you are using hierarchies with Slice to see obviously supports hierarchies throughout and maybe you design some MDX forms as I just presented, won’t be able to do is if you are using hierarchies with Slice to see obviously supports hierarchies throughout and maybe you design some MDX forms as I just presented, that won’t run obviously in hierarchies, right? So that’s probably the only caveat. But of course Slice can run Perspectives reports without problem. Okay. And one more question. Sure. Is there an official deprecation date for TM1 Perspectives? That’s a good question. I wouldn’t know, wouldn’t know exactly the date. That’s probably more a question for IBM, but for whatever the date might be, we are ready to back you up with Slice. All right, cool. Well we’re pretty much at the end, so well there might be one more question. Let’s see. And by the way, guys, you have on the handouts, you can click there and download the links I just show you on screen. So feel free to download, you have all the information you need there to download Slice play with it, and, and try to build something as yeah that handouts that last couple of slides that you showed, right? So yeah, download and yeah, I’ll just pop up the feedback form and we’ll close off. Thanks very much Louis. No Worries. Thank you everybody for your time and keep enjoying this great conference.

See you around.