SAP mentor and BI 2013 speaker Joshua Fletcher stayed up late in Australia to join us for a one-hour live chat on universe design.
In this preview of some of the topics he'll be covering in his Singapore sessions, he covered everything from universe design fundamentals to technical questions on setting context joins and converting from UNVs to UNXs, as well as an extreme example of bad universe design.
For Josh's tips and advice, read the transcript of the Q&A chat here:
Moderator: Josh, thanks again for joining us today. We have a lot of questions coming in, so great to get started.
I know it’s late there – so thank you for taking the time for this followup Q&A. If we could send you a coffee right now, we would!
Joshua Fletcher: Hi everyone, thanks for coming along to this Q&A. Excited to be with you for the next hour.
Scott: With Information Design Tool, what are best practices for managing security and access restriction requirements?
Joshua Fletcher: Hi Scott, there are several layers to this. Hopefully you would be restricting access to the IDT application itself. From there, you would use the CMC security lock down access for universe designers. You would most likely also want to secure access to certain universes for certain designers. There are also additional levels of granularity around the universe security you could add.
You then also have additional levels of security, such as access restrictions, within the universe. As this is quite a broad topic, and dependent on the business requirements, I'd recommend getting familiar with the different options to best be able to implement a security model that works best for your organisation.
Paul: In BOXI 3.1 I have one dimension UNV with about 50 dimensions and about 20 'data mart' UNV's that link to that one dimension UNV.
How would I accomplish something like this in the IDT? Because I believe linking UNX's is not possible?
Joshua Fletcher: Hi Paul, the new separation of the Data Foundation and Business Layer should be able to achieve this functionality for you. The Data Foundation would be maintained once, and multiple Business Layers (your 20x data mart UNVs) could leverage this.
Linking UNXs is a highly requested feature so SAP are hopefully considering how they can add this in future.
Ken: Hello, I'm curious if you've seen any issues or pitfalls to avoid in any new features in IDT, like User Attribute Mapping?Thanks!
Joshua Fletcher: Hi Ken, I unfortunately hadn't had time to play with the user attribute mapping as much as I would have liked - so haven't come across any pitfalls in this area.
From my experience, the new DF and BL separation, combined with broad EDWs, mean you need to review your development processes and align them with the new way of building your universes.
Also, the multi-source capability, whilst awesome in potential, has the ability to cause serious performance issues on both the report level, BOBJ server and database impact if not well tested and tuned. This is an area to invest time in making sure it works well.
Guest: Is there any option to change single source universe as Multi source Universe using IDT?
Joshua Fletcher: Hello. Sorry I don't believe this is possible once you've created a universe as single/multi source. Maybe something to log on Idea Place?
Darren: When setting context joins, should you specifically allow or deny all joins? In what circumstance would it be better to leave a join neutral?
Joshua Fletcher: Hi Darren, great question - I love talking about contexts :-)
Firstly, you should verify all your join cardinalities are set correctly. Then, detect the contexts required. This should automatically set the majority of your joins to what is recommended.
If you were to do it manually, only joins that are part of the context should be Allowed. Any joins from opposing contexts should be Denied. Any joins that aren't part of any context should be Neutral.
Guest: If I want to connect to BEX queries or BW infocubes to BO, do I need to build universe?
Joshua Fletcher: Hello, thanks for your question. It really depends on your use cases and what BOBJ tools you wish to deploy. The recommended approach is to use the Analysis suite of tools to connect directly to the Bex queries using BICS. Crystal Reports for Enterprise, Web Intelligence and Xcelsius Dashboards also connect directly to Bex via BICS.
If you have a scenario where you need to use Explorer/Lumira, and/or a scenario where you need to combine BW data with non-SAP data, only then should you create a UNX on top of the BW InfoProvider.
Ram: Hi, Can we link DSO’s and Bex Query in one Universe? If yes, how do we do that?
Joshua Fletcher: Hi Ram, I don't believe that's possible. The current UNX only allows connectivity to InfoProviders, not Bex queries.
You *may* be able to achieve this at the report level, such as in Web Intelligence, but I have not tried this.
Madhu: Designing a universe - Best Practices?
Hi Madhu, there are lots. A few years ago I posted a list of best practices, most of which still hold true. You can review it here. Let me know if you want more detail of any area.
Ram: Hi, If you want to display hierarchy (which is in SAP BW), how do you do that in IDT?
Joshua Fletcher: Hi Ram, the UNX does not support multidimensional hierarchies, only relational 'flat' hierarchies. If you need OLAP capabilities, you should use the Analysis suite of tools in the BusinessObjects portfolio.
Andre: I heard that SAP may re-introduce derived/link Universe in the new IDT, why was it not part of the original design? If so, will the Upgrade Mangement tool convert the unv to uvx?
Joshua Fletcher: Hi Andre, I can't speak for SAP so not sure why it wasn't originally included, or whether the UMT will upgrade UNVs to UNXs. If this is an area you are keen to be kept up-to-date on, I'd recommend engaging with the ASUG Influence Councils, the Ramp-up program and the like - there are lots of ways to give more direct feedback to SAP.
Moderator: Thanks to everyone for joining us today, and some great questions!
We're seeing lots of questions for Josh today, and we'll make sure we get to everyone's post. (All your posts go into moderation, but we'll be posting them shortly as Josh answers them - they haven't disappeared).
Back to Josh, who certainly needs some coffee now...
John: I see there is conversion functionality in the IDT to convert UNV's to UNX's but I have had mixed results where joins seem to be altrered between the 2. Is this an issue that they are looking to resolve soon?
Joshua Fletcher: Hi John, it is definitely a case by case on how the origin universe is designed. Of course any conversion process (I'm looking at you Report Conversion Tool) is not perfect, and you should always back up a conversion project with lots of technical and business testing.
If you have specific universes which cause the issues, this may be something you can feed back to SAP to help them improve the conversion tool.
Scott: When I design a Universe I use left and inner joins, but when I run a query and view the script it shows right joins and I always question the results. Any idea why you can't force Left Outer joins?
Joshua Fletcher: Hi Scott, I have no idea about this, but don't forget that the IDT generates different SQL based on both the database driver being used as well as the database parameters you've set. Maybe something to explore further?
Scott Wallask: Hey Josh -- Nice to see you on here and thanks for staying up late in Australia. I know when we've talked in the past, you mention that users need to get business value from a universe. How would characterize business value in this case, what does it "look" like?
Joshua Fletcher: Hey Scott, you're welcome. In regards to business value, I think a universe should always be designed with the end-user report developer in mind.
I've seen examples where the universe is basically a copy of the database structure. For any report developer who isn't database savvy, this is very confusing.
While that's a really bad example, there are many ways universe develops can deliver business value. A continuous improvement initiative around all universes will deliver value, just by letting your BI devs know that their input is sought for how to improve the universe.
Other examples would be flesh out the dimensional hierarchies, set object formatting, explode out date fields into components like Year, Year-Month, Month No and Name, Day Name etc - and of course, deliver a universe that *always* generates correct SQL. Wrong numbers are the quickest way to kill the usefulness of a universe!
Aqeeq: I've noticed something in IDT 4.0 till SP5 , when I click on a table and select "refresh structure", it refreshes entire structure of all the tables. This is different from Designer in BOXI 3.1 when only the selected Tables would refresh. Is there a way to replicate that functionality in IDT?
Joshua Fletcher: Hi Aqeeq, no something I've tried myself I'm sorry, so can't comment on if this is possible. If anyone else can help, please chime in.
Guest: Hello, if you want to search the hierarchy - can you do that? Not just LOV, but the whole hierarchy?
Joshua Fletcher: Hello, do you mean a Bex hierarchy, or the relational 'flat' hierarchy?
Guest: Flat hierarchy
Joshua Fletcher: Hmm sorry I haven't heard of a requirement to search through the complete hierarchy, and don't think it's supported in a simple way. There would be a way to achieve it, I'm sure - by flattening the values into a single dimension, or with input controls perhaps.
Someone developed a cool solution searching with input controls across multiple values - maybe this could solve it? blog.davidg.com.au/2012/04/search-field-...
Venkat: What are best practices to get better performance by using the multi source enabled universe (unx)?
Joshua Fletcher: Hi Venkat, to begin with, ensure you are working with your respective DBAs to tune each database connection in your multi-source universe. Set the parameters appropriately, and keep an open dialogue.
Once your universe is being developed, look to use the Data Federator Administration Tool - I've heard case studies where this has improved performance massively, just by tuning for common queries.
Also check out this great blog by Jon Haun
Ilan: Can you provide any guidance on performance tuning in the IDT? For example, what steps do you take?
Joshua Fletcher: Hi Ilan, take a look at my response to Venkat above - the first part also applies to single source universes. You should also spend a lot of time with the SQL generation, look at things like using index awareness, aggregate awareness and the like.
Philip: Hi Josh. Great session! When generating a UNV Universe on a SAP BEx query (with variables) we notice that sometimes the filter key is generated with an arbitrary ID e.g. .. on other occasions it is generated with the technical name of the BW variable. Any ideas why this happens?
Joshua Fletcher: Hi Philip, great to have you along! I'm sorry, but haven't come across that scenario myself. Have you noticed that it only happens with certain Bex design, or taken it up with SAP support to try to resolve it?
Darren: What is the best way to apply row level security across the board in the universe? Do you make use of the data security profiles or would you recommend simply forcing a filter to be applied in all queries?
Joshua Fletcher: Hi Darren, you can create a filter object and then force it to always be included in queries. This would be useful for very simple row level restriction, however if the row level restriction needs to be done by user/group, then it would quickly become a maintenance headache - so then data security profiles would be best.
Moderator: Josh, This question was posted to one of our forum threads by Dimetry. Maybe you can shed some light on this issue?
I have a question regarding reconnecting my dashboard to a new universe.
Last week I tried to re point my dashboard to a new universe, when I found that this is not an option. The old universe became corrupted and not usable so when I tried to re point it to a new universe I got an error; the dashboard was looking for the CUID associated with old universe and the only thing I have to do is to re build my queries against the new universe.
Is there is any better way to re point dashboards to universes different than keeping them together or rebuilding the queries , as for reports you can reconnect to a new source of data by setting the data source location under the database tab . Thank you.
Joshua Fletcher: Dimitrey, Unfortunately it's a known issue with Dashboard 4.0, and from the what's new in 4.1 (see here) it doesn't look like this has changed. I haven't tried it in 4.1 myself yet, but unfortunately you'll need to look for workarounds - such as using BI Web Services to act as a front for the query.
In future, you can avoid universe corruption by doing version control and regular backups using LCM.
Guest: Any advice on how to get started with 4.0 in general, Webi and UNV dev?
Joshua Fletcher: Hello. There's lot of videos and content all over the web. You could start with the product tutorials on SCN, like scn.sap.com/docs/DOC-7725. You can subscribe to an Amazon BOBJ instance also, but this is a little costly. I think there is also a trial of BusinessObjects Edge (for the SME market, but exactly the same functionality as BusinessObjects) floating around somewhere.
Guest: I believe the ability to have multiple BL's is the most poweful feature of the IDT. Do you have any recommendations on how best to mange these and how to roll them out to the different user communities as well user training issues?
Joshua Fletcher: I agree it is certainly a great new feature that is very powerful.
Once you publish the BL, it appears as a regular self-contained universe, and has no ties back to the central DF.
Therefore, your different user communities and training hopefully aren't too bad - they would be the same as in XI 3.1. I would spend time on getting the development process right to leverage the opportunity on only needing to maintain one DF for multiple BLs.
Dave: Josh, would you say the IDT lends itself to a more collaborative design process or environment? If so, any examples? Thanks.
Joshua Fletcher: Hi Dave, I interpreted your question two ways, so I'll reply each way - hopefully one of them is what you meant :-)
Firstly, in terms of collaborating with the business during development, I think the IDT is much easier to do this with. For instance, the new data profiling functionality in the DF, and especially the much-improved Query Tool, means you can test the output of the BL without leaving the IDT. So you could invite your business users over to your desk, run them through some queries, and get direct feedback without going through the old process of Export Unv > Open Webi > Test SQL > Update Unv > Close and Reopen Webi etc etc.
Secondly, in terms of collaboration between multiple universe developers, you now can use shared projects and synchronisation. So this is also much better than the UNV way of doing this.
Philip: Refreshing a UNV Universe based on a SAP BEx query can sometimes take many hours. During the refresh we can see repeated reads on the RS* tables in the SAP BW system. Any tips how to speed a refresh up!?
Joshua Fletcher: Hi Philip, sorry working in BW is not my area of expertise. I have seen UNV universes querying Bex queries very quickly, so that tells me the BW system, the Bex query or the BOBJ server needs to be tuned. You should investigate in those areas. Sorry I can't be more help.
Ilan: Within the business layer, a developer can also create views. Can these views be used as another layer of security to restrict access?
Joshua Fletcher: Hi Ilan, yes you can create views both in the DF (which are more for working on specific areas of the DF) and also in the BL. The BL views can be used to present an alternative view in the Query Panel, but are also securable in the Business Security Profiles.
Venkat: Is there any new features are available in upcoming version of IDT?
Joshua Fletcher: Hi Venkat, there sure is - lots in fact. The one I'm really excited about is the federation layer. This allows you to build a simple virtual ETL process between multiple sources in the DF, that then are presented in a federated table within the DF. I'm hoping to post up a video example soon on our YouTube channel. Take a look at the Semantic Layer What's New in 4.1 series on SCN, too.
Amresh: Can we take multiple data source for OLAP universe?
Joshua Fletcher: Hi Amresh, no that's not possible - OLAP universes connect to only one OLAP data source.
Amresh: Thanks Joshua
Darren: Thanks for the tips Josh! I have contexts created around the different fact tables. Sometimes a user wants to create a report that is just the joining of 2 dimension tables, however, in this case they will get prompted for a context. Is this resolvable with a shortcut join?
Joshua Fletcher: Hey Darren, you are very welcome!
This would most likely be a separate context by itself, if the dimensions join directly. Otherwise, a context selection is necessary, as what fact table does it relate to?
For example, in the universe training, we cover a scenario as follows:
The two contexts are:
Client --< Sales >-- Showroom
Client --< Rental >-- Showroom
In this case, if the user selected Client and Showroom objects, how do the two relate? Is it Clients who bought cars from a Showroom, or Clients who rented cars from a Showroom? Hope that makes sense :)
Moderator: With that, we will wrap up today's Q&A. Thank you too everyone who joined us today. Great discussion! If you have any comments about our new chat format, we’d love to hear your feedback. Please be sure to send it to us here.
For more from Josh, be sure to follow him on Twitter at @josh_fletcher and you’ll often find him on Diversified Semantic Layer here. You can also read his past Q&A here on universe design.
And we hope to see you all at Singapore at BI 2013 -- be sure to stop by Josh’s sessions on 4 and 5 September to meet Josh in person!
And Josh, thank you very much for sharing your advice today.
Now, please be sure to get some sleep, and we’ll see you in Singapore this September!
Joshua Fletcher: Thanks everyone for some great questions, hope you are able to take away some useful tips. See you next time!