Powering insights for SAP data with Microsoft Power BI
So, let's talk a little bit about what. We're going to accomplish over the next few minutes we're. Gonna cover first. Of all an overview of power bi and, before, we go further how, many of you already use power bi I'm not familiar with it you are okay great you. Do okay so we'll do a little bit of a high-level overview before. We move on to more specific, topics, the. The, second, area we will cover is a deep dive into the connectors, that we have for s IP particularly. Let's, say P HANA and the shape e-business warehouse and. Then we'll talk about the roadmap, for the rest of the year for these two connectors what do we plan to do in, terms of engineering investments. So. What's power bi power, bi it's, Microsoft's. Self-service, bi service. Built, on top of Usher is a cloud-based it's. Cloud first but not a cloud only solution, because. It does support, also. Connectivity, to on-prem data sources and that's basically. On Prem data sources connected through the power bi get away from, the power bi service sitting. On a show how. You actually start consuming data from power bi the, easiest way is actually, use let's call the ABS ABS, our out-of-the-box, content, for well-known SAS services, so for example, things, like Salesforce, Google Analytics, dynamics. QuickBooks. And. Many. More we have 50, plus I think it's almost 100, plus now, different. Apps for out-of-the-box, connectivity. All, the users need to do is just punch in their credentials, for example their Google Analytics account and then they get out-of-the-box. Dashboards. And reports, that will basically give them insights, valuable, insights out of those accounts, in just a matter of a few minutes. Besides. That out of a box connectivity. You can also use power bi to create, custom, content to create reports. And dashboards on top of your own data so. For example, if. You have enterprise. Data Mart's if you have sequel server you have Oracle. Databases, my, sequel, universe, AP Hana ICP business, warehouse you, can definitely also, build your own reports, and dashboards on top of those data sources, and for that you'd use the power bi desktop tool, so, think about this analogy, the, power bi service power, bi calm, as a website or, even one of the mobile apps to consume, data it's, just a place where a business you said would go to consume, they think about it as YouTube. Is the YouTube for your business data your business intelligence reports, and dashboards now.
In Order to create content, that's hosted, in YouTube you need to record that content and you need to publish it right you, do something that can't a CEO or moviemaker. One, of those tools and for, that the, equivalent, in power bi wall is power bi desktop that's the tool you use to create the content and then you publish into, the power bi service and. Then from there you can schedule. Refresh, to keep it all up to date up. To I, think eight. Times per per, day and, the basic configuration but, you can also go unlimited. With the premium, version of, power bi as, well as you see what's called direct query which, basically allows you to create a live connection between your, visualizations. And the underlying data source back end so you can actually for. Each user, interacting, with the report you, could issue queries against the underlying back-end if you wish so they get near. Real-time results. Besides, our website there's connectivity. And consumption experiences, from mobile applications, we, have mobile, clients, for iOS, Android, Windows Phone and so on as. Well as a iOS. So. Talking a little bit more about power bi desktop power bi desktop the, main report authoring tool for power bi, it's, a download. Component. It's free completely free you, can go power bi calm and download it from there today and just give it a try doesn't, require any license doesn't require anything to start using it and kicking the tires and. It's a great way to start getting, yourselves familiarize, with Paul VI. Now. You'll actually use it to create content that you publish into the power bi service and. It's optimized, for that workflow, so it guides you from getting, some data into the tool doing, data preparation. Data, transformations. As we all know data, is never in the shape that you need for criminal report or doing analytics, you always need to massage that data so, you just power the other stuff for that and then it gets used to creating those report. Pages with multiple, visualizations as. You can see here with, a lot of capabilities, like. Formatting. Conditional, formatting, ability, to cross filter, visuals, based on selection on another visual, and so on and. We update power, bi desktop every, month the, first week of every month, we release a new version with lots of new features in, fact US, version went live just last night with. At least 20. Or so different features. We'll, touch base on some of those here because some of those impact the the BW, connector that we have. The. Other component, that I mentioned, from the high-level overview is, the on-premises, data gateway this, is the component, that will allow you to reach, on-premises. Data sources, like VW.
Hana Other. Relational, databases from. The power bi service so, from desktop because, you're sitting in your home premises, network you connect directly to, those back-end data sources once, you publish the report to the power bi service that's, a cloud-based service you'd, need to configure the Gateway to establish, that connection from cloud to UM planner now, the, data gateway it's a secure, one-way. Communication. Channel that. Breeches. From cloud, service that research, power bi can also be used with other Microsoft. Cloud services like Ashura analysis, services power-ups, flow and, other things it, bridges down through a short service bus to, connect to your own premises, data, gateway agent this is the component. That you install. In one of your network machines and, then from there you'll be able to connect and route the traffic to, the specific. Data source backing that you're trying to connect, it. Does take, care of all of the credential, management, in a very secure reliable way, it's. All configured, out-of-the-box for you there's. A dedicated experience, within the power bi service to. Register, the data sources you would like to, access to a specific gateway, and. To configure with credentials to use what. Data sources, or security, groups within your sorry. What users or what security groups within your organization's. You would like to grant access it, allows you to do advanced. Configurations. Like for example clustering. Of multiple gateways so you can, set, up high, available, solutions, with you know load, balancing, and those capabilities, as well, and, it's you know very calm to complete, enterprise, gate Enterprise. Great way of accessing data. Sources on-premises, from the power bi cloud service. So. With that said those are kind of a foundation, on power bi overview. And main components, let's, talk about specifically. The, connectivity, options from power bi to s IP data sources we, have two built-in connectors, one, for Hana and one for business warehouse, and. Then we also have what's called the custom connectors SDK, so this is a completely. Open SDK. That will allow you to create connectors. For any data source back-end, that you can imagine in, the wall and. That could all be easily, pluggable, into power bi both, in the desktop to create new reports, as well as in the on-premises, data gateway to keep that content from the cloud refresh, if it happened to be an on-prem data source. Let's. Talk about the Hana connector. So. The Hana connector, is a built-in. Connector, for Hana in power bi desktop it's. A really. Specialized, and tailored for Hana before. I go deep how many of you already use the Hana connector, in power bi or. How many of you have Hannon backends. Today that you'd like to get into power bi connected, from power bi none. Of you have Hana. Give. You a couple months okay how. Many of you have BW, though okay. So the next part will be more applicable but, once you guys get, into Hana the. Good news is that you'll be able to get data into power bi very easily. Our Hana connector, is been endorsed. And certified, by ASAP, we've. Hit, official SAV certification, as of a couple months ago it's. Built on top of a Hana ODBC, driver that's obviously, driver developed, by ASAP and released by, them so it's. Kept up to date with new versions and updates for Hana and you know bug fixes and that kind of thing now, talking about the capabilities, of this connector, it will allow you to connect, to Hana we use those analytic, views and calculation, views and it. Will give you a very tailor experience, for browsing so it will do things, like supporting hierarchies. We have two different modes relational. More than a multi-dimensional, mode, for this connector the, relational, mode treats. Hana caiaphas, tabular. Source. Database so, some of the concerts in Hana are not propagated.
Through Things like herpes, non-additive. Measures those, things are fully supported. Through the multi-dimensional connector. Which we, introduced about four months ago. This, connector gives you full full. Fidelity. Capabilities. Multi-dimensional. Capabilities, from kana which, is actually, something that no other third-party, bi tool besides. A sepia native tools would do on top of Hana today so if you're considering other. So series behind tools on top of Hana the. Good news is that Microsoft, will give you the, right data results, for. Things like non-additive, measures which is something that other tools won't be able to do or propagate. Hierarchy, so you can do drill. Down in your reports, and hierarchy based filtering, and those kinds of things so. The power bi connector, does that in, the multi-dimensional flavor. On. Top of that connector we enable enterprise great, capabilities, from power bi so for example, the. Important, data query modes as we were defining, a little import, being ETL, data. From Hana or any other data source back-end into, power bi directquery. Being, that live connection between power, bi reports, and visualizations, folding. And pushing down for the execution, to the data source button. It's. Supported through the on-premises, data gateway so you can access your Hana servers, on Prem and, when. You when you use directquery. Mode you. Could also configure. Single, sign-on with Kerberos, this, means that you, can have these data quality reports, published to the power bi dot-com, and besides. Having. Them issue life queries against the underlying data source when, users in tera with the report you, could do this also with your specific end user credentials so think about this you, have a user in power bi that's maybe, Miguel, at contoso calm right and, you also have somebody else like Ana at contoso, calm they're, both looking at the same report, from their browser or from the power bi mobile application, and they're, interacting with a column chart for example to look at product. Sales by product, category or something something, I was like. If you configure single sign-on you're, going to be able to have an. Ax at contoso, comms credentials, flow, down, to the on-premise, data gateway and that, resolved, through Kerberos, constrained delegation, to. The actual, user in the Hana data source button that. Should be used for Ana's, basically, maybe. It's a domain. Account like Ana like. You know domain not Ana username. Something. Like that so you'd be able to get an, on the data that she's supposed to see under her specific, security. Model or role permissions in, the underlying database in. Hana so you don't need to recreate all of those we, know those are very complex to set up in Hana today so, you don't need to reconfigure. Them again in power bi or anything which is flows, through the same goes for the other user so you could have two, concurrent, users looking at the same reporting, power VI getting. Different, results by, based on the underlying credentials. In the data source. With. That said let's go and do a quick demo of the Hana connector, and, I'm going to use power bi desktop for, that. So. I have it launched here this is the power, bi desktop free, tool that you download and install on Windows machines. Let's. Actually go and create a report so, the first thing we need to do is go get some data. Let's. Go get data and, this will take you into the get data dialog, so here we have about 85. Data, sources we. Actually support way more than that because we, have connectivity to generic interfaces, like, ODBC. Already, we all their breast, api's. Yeah, so the question is whether the, VW connector and the Hana collector are included in the free version yes, everything, I'm showing you in the desktop tool it's completely, free you download. It you can use it today there's no signup required, or anything there's no license. Required the.
Licensing. And the signup is needed whenever you want to publish content to the power bi service for. Other users to consume with, power bi desktop you can create local, files just like you, would create an Excel workbook you could add power bi desktop report. So. Let's go and look at some of these connectors, we. Have multiple. Connectors for files things like Excel, files, JSON. Files XML. Folder. And SharePoint folder is a very interesting scenario so the scenario here is connecting. To a folder with a bunch of files where, all of these files have the same data. Structure think about it as you. Know maybe logs that you get on a weekly, or monthly frequence. That, all have the same format that you would like to bring them into a single bill, into a single table so, you could use that connector, to combine them into a single view and then create reports on top of that. Relational. Databases we. Have a bunch so we have sequence. Server uh sure sorry, simple server access, database analysis. Services, we, also have a wide, range of third-party databases, things, like Oracle db2 Netezza, my, sequel Postgres, they say pre sources, show, up here so Hana BW for, Vidal you we actually have application. Server and message server we, also have connectivity to other cloud-based sources, like great chief in Palo bigquery, a snowflake. We. Also have connectivity to power bi datasets themselves, or things that other, users have already published to the power bi service could. We use as the basis, for new reports, to be created here uh. Sure. Sources, and ishow services, so of course when, Microsoft's. Bi o we have bursting, market connectivity, to Microsoft, Azure cloud services, kind, of redundant. Thing to say but we. Have connectivity, to all and the most used. Other, services, out there like database, data warehouse. It's, the insight Hadoop table storage blob. Cousteau. DB it's a new source coming out spark, lots. Of em so. When I go to the Hana database, connector, I see the connectors, dialog it's, gonna ask me for the server name and then I can also specify a, port, by, default we would use the IP, default for single container which is a common case most likely for most of you you. Could also use the multi container this is for multi tenant databases, that are, hosted. In the cloud in. This case I'm just going to use the single container and. This is the hardest part of the demo what I need to do a copy paste with just one hand so. Bear with me. Ctrl, C. Okay. Got. It so. Now we have my I have my cell where we're going to use the default single, container port here's, what I pick one out what do I want to use the import or ETL mode, versus, data, query so I can pick direct quarry if. I was using import, I could. Actually as an advanced option I could specify a seacoast, element that I wanted to run that at least through the other VC driver in. Most cases though you, probably don't have that statement, so you could just use the, power bi desktop experience.
To Connect to the Hana view, that you want to use and then create, the report on top so. I'm going to browse my. Hana server and I have a bunch of views available to me. Let's. Go for example under my adventure, works database and, I have these car, sales view. Once. I select the bill I get, to specify any input, parameters, or input variables, for that view so in this case for example the. Video is defined such that I could specify, make. For, the for the cars and if I go into these daughter daughter experience, we actually get the option to specify which, value in this case the, vu defines, a set, of predefined, values that I could specify like the. Different makers for cars I could, pick it from this list you know there's nice capabilities, here like search so I could start typing, and just get a subset, in, this case because there may be some tional and the same goes for style, I'm just gonna leave them blank but. I could pick any value and then apply and then get a preview for. What that would look like this, case, may just say load let's. Say load and, now because this is that equity report all we're going to do is just load, the definition, of that Hana, calculation. View. Into. Our main power bi desktop report, bill which. Is this one you've seen right here so here you're seeing a blank canvas when, we're going to start adding data visualizations, you. Have the visualizations, pane so you can pick what, type of visualization you want to use you're gonna use things like bar, charts, column charts, cars gates. White, charge donuts, you know bunch, of things but also support custom this was through that option and there's a vibrant, community of visualization. Developers. Were contributing, this was the power bi these, are go through, these. Three framework. This little script framework, we. Have a few Spain which is basically the. View on top of all the data that you've already loaded, into this report in this case because. We're using direct, query as a storage mode this, is just the metadata definition, so, now we can start doing things like for. Example selecting. The sales, amount. And. That's gonna add a new visual so now that I have a visual, I can, pick a different visualization, type, so if I pick a car you. See this little spinner here this is actually telling me that it's querying, Hana this, home from Hana database, and it's running that total sales aggregate, the, total sales measure and, it's bringing back the results, so, now I can format this whichever, way I want maybe now maybe I want to do this as a gauge and, I can start applying formatting, options now, I could also build more complex visuals, so, pick something that has a dimension, for example if.
I Go to the, list. Of makers I can, bring that dimension. That. Brings me those two values if I turn this into a bar chart I can, now drop, the total. Sales amount into. The volume. I'm, not going to show me those - of, course I have lots of formatting options here as well so I can come back and say well I want to show data labels, maybe, I wanna change the, color so. Maybe maybe. I want to specify a different colorful contour, so. I'm. Not the best at picking color so bear with me but just to show you the, range of formatting, options that you have in a visual you could, do things way more complex, than that so for example, let's, say that in this case I didn't, get any hierarchy from the hannahb you because there wasn't one but, I could define define one myself, so for example I have me when, I have the model, let's, say that I wanted to build a hierarchy, on, top of those two so, I'm gonna create a new hierarchy new custom hierarchy with, make and then I'm gonna go pick model, and. I'm gonna add that to the name, Huracan. So. Now if I add, both. Of these and. I turned that into a column. Chart, I'm. Gonna get the SPECIAL controls here on the top you see these two controls here that's. For drill down capabilities, so, now the moment I drop something like the ourselves amount, into. The value we're. Gonna query for that data and we're gonna get the top level so this is the level one of a hierarchy, which was make I'm, gonna get total sales by maker which effectively, much is this other parts are very hard over here if, I turn on drill down I can. Now just click into one of these columns. To. Drill into the next level which is basically gonna, give me the. Level. The. Lower level which is the model you can actually add, another level, to this hierarchy, like a style. So. Let me actually get, rid of this guy. So, I'm gonna get rid of that again I'm gonna have their style first. And, then we can also add the model again. So. Now if I show all of them I'm, gonna have three levels on this hierarchy, so I turn drill. Down on again and, I go into Kanto so now the second level that I'm gonna see is the car style these, are hot spots pores SUV and so on now, I can drill one more level for example to SUV, and, I can say that there's one model has to be the contoso mates that's. A mountain 300, black. Similarly. I could back up. To. The top level and rather than drilling down into a specific product. Car, maker I can, click, into the drill all down to the next level so I'm gonna see the, breakdown across. Both, makers, for, all product categories and, I can also do. This at the bottom level for all the models so. I get, all of that I. Can, also mention earlier about cross filter capabilities, so those come completely, out of the box in power bi desktop so, if. I click now into Kanto so we, automatically, cross filter, the total sales as well, as these guys so as you can see in highlighting, these, guys because those are the mobiles are belong to control, so if, I go back up one level. To. See the categories. And I pick control so again we're, actually gonna subset so this is the total sales for diesel, cars across these two makers and this is for Kanto so and this is for adventureworks. So, all configured, out-of-the-box I didn't go how to do anything, at all the. Last step in this process would be to, publish your report to the power bi service this. Is the part that's actually recording, a licenses that that would be a bad thing which, by the way you get a free trial for I think 30 days after, that period you have to purchase about their license but, all our I showed up to this point is completely free and, you get all of the capabilities, available, out of the box. Now. Let's go back to the power, point and, let's talk about what's coming for. The HANA connector for the rest of this calendar year I mentioned. Kerberos, based single sign-on or something that's already available through, the Gateway we, know that many of you would probably already have some old base capabilities, on top of kana we're, gonna add some obey single sign-on to power bi, Hana. SSL, Certificates, support this is already supported in the desktop but it's not there available through the service and the CAD way that's coming also over the next few months in. Terms of authoring, capabilities, we're going to be improving, the user, experience for, him providing, variable values, like the input variables, experience that I showed in, that navigator dialog. Things. Like supported ranges, exclude, exclude, values, and. That kind of experience more. Flexibility, in defining custom, measures so I'll show you how to interact with out of about measures like sales amount you, could define your own custom measures.
Which, You could today, but. You don't have full flexibility, you only have a subset, today so, things like sum average and so on you'll, be able to apply in, the the next few months. Beyond. This calendar here the ability to modify the value the values for those variables, in the power bi service as, someone consumes the report and then recording, the underlying data source, which. Is something not about today, it's a loved one in desktop only. So. Talking about BW, how. Do we W connector again kind of the same deal as with Hanna it's, fully endorsed by ASAP it's certified it's. Built on top of an SMP component, we. Have a big one version of this connector that's built on top of netweaver and beats. You implementation. It's built on top of sa p.net connector, it's the one that will released just this week it's, currently in beta in power bi desktop with. The jeune power v adjustable ease and. It's going to hit the power bi gateway. Later. This month so. Talking about the connector capabilities, support. Without you seven point and, great you know depending, on what the netweaver RFC, supports, and what the nut connector supports, we, support both the application several and the message seven which is a load balancer we. Also support connectivity, to info, cubes and query cubes including Bex queries so if you define a Bex, query and your bag stalls you have an option to expose, to third-party clients if you check that box you'll actually get those expose in power bi as well we. Have a multi-dimensional, connector. So similar to the Hana multi-dimensional. One that I showed you it, has full support for hierarchies, and variables, and, all of those, BW. Specific, multi-dimensional. Concepts, support. Also for member properties, including custom. Properties. Kishore. Medium-long tags and all of those properties, that you probably, define in we love you. You. Supported through the Gateway both important, directory moves. And. You know it's available there today we don't have single sign-on yet. We'll, talk about that later it's coming over the next few months in the roadmap so. Talking about the beach conversion, of a connector this is a one that Shiv, just this week, it's. An implementation it's, based on a different component is a safety-net collector, collector. Significant. Improvements, particularly on performance, we've. Been running this connector in a private, preview mode with a few customers, over the last couple months these, are some of the testimonials, about 60. To 75 percent faster than our previous implementation and, extracting. Twenty thousand records takes, less than two minutes much, faster the existence. Of our ABL tools like MicroStrategy. And so on. Compared, to our b1. Connector, it, used to take 30 to 40 minutes now it takes only 2 minutes so that's a pretty impressive performance. Improvement, ability. To retrieve several million, rows of data so this dotnet connector, has, specific. Modes optimized, for both extract, of data that's called a compress mode you, can set our connector, in that mode so it's just given, your higher throughput. Better. Detection for today variables, and reflecting, date and time as as dimensions, before. We were only able to do them our stacks because the previous interface, didn't allow for that. That configuration, we. Do better exception, handling and more error-prone we. Have folding. Of columns so that if you're using only, a specific subset, of columns, in your queries we, now have the ability to just request, the specific, columns that you're using in your visualizations, so, it firstly, embedded, optimize the, performance as we were saying earlier it's.
Available In the June update released yesterday for, desktop and then for the Gateway coming. In third week of June that's so, for now you're going to be able to create reports, they starting third week of June you'll be able to publish, them to the power bi service and. Refresh them through the gallery, so. Looking ahead in terms of roadmap we're, gonna be having support, for message server in the service, for refresh, today, it's only a desktop feature, application. Server is supported in both desktop, and service. Carriers, base single sign-on and top of both application, and message server is coming over the next couple months as well, performance. Improvements, or optimizing, the navigator metadata. With three or four dimensions, measures, and then optimizing, the way we query from visualizations, on directquery mode so, the, interactivity, with resource is much better. In. Terms of enhancements. For authoring, kind, of the same kind of investment, compared to Hana on top of the input. User, experience, for variables. More. Metadata in directquery mode so, for. Example formatting, bringing along currency, or units of measures with, your measure resolve values things. Like you know revenue, and, scale, units, like mirrors. Versus, you know inches. And that kind of thing and then, certifying. That be true implementation, with features released this week which were hoping to the organics carbon ones as well. In, terms of more information, we. Have a lot of documentation, around Ahana and the VW connectors, in the official power bi, documentation. We, go to power, bi comm you'll find that there's documentation. You go there and you search for Hana VW, and you'll get all, of these documents including. The NSA, PBW, white paper including, Hana, data query guidance, for whether to use tabular, versus, multi-dimensional. Modes. And what are the pros and cons of it single. Sign-on which is kind of a very technically, deep area, and how it'll fear that Kerberos. Constrained allegation. It's all explained, step by step on this article and. Then we have the custom connectors. Github. Project. Which is basically where they go to learn how to build your own connectors, for power bi in, case you're interested on that. With. That just a recap we cover a power bi overview, and then a deep dive on these two connectors that we have and the Roma for the worst of this year I hope, you guys liked the session and if. You have any questions or they hanging over here later this afternoon also. Feel free to hit me up thank. You.
2018-07-12 23:03
