Best practices on Smart Import object & data control

Etienne CAUSSE

Hi everyone,
I'm doing some testing on the new summer release to evaluate how some new functionalities can help in our internal roadmap. In particular I've been testing the Smart Import object which will definitely help us solve some use cases.

I have tested the following settings which seem fine :

• Add or discard new entity members like in a data reader are quite useful.
• The "client filter" allows to force some values according to process parameters, which seems also relevant.
• The "discard outside selection" also allows to control which kind of data is uploaded. I did not test regarding selections linked to authorizations but I guess it works the same way ?
• Validation rules can be setup with the formulas.

However there are two things that don't seem native in that object:

1. How can we control the input according to the values of another cube ? 2 use cases for me there:
   1. When uploading expenses by P&L nature and destination, I would like to enforce some rules, especially only some combinations of nature and destination are authorized. Today in a data entry, I use a filter that show up only the authorized combinations based on a filter cube.
      How can we do that in a upload ?
   2. I would like to make sure that when a user sends a sales value for a given organization and SKU, this SKU has a registered unit cost in the system. Again this info is stored in another cube, which might have different dimensions than the cube I'm filling in the upload.
2. It seems the copy / paste part does not take into account the locale of the user, same problem that we have when doing copy/paste in dataviews. For example my Excel uses a french locale, with a comma as a decimal separator. If I copy / paste in the new object, the comma is shown but not recognized when importing the data, which is then wrong.
   This issue does not happen when importing a file through the direct upload function. So two questions :
   1. Is it a known bug regarding locales ? Very painful to explain to everybody worldwide that they should replace commas by points for decimal before copy/pasting..
   2. Is there a way to disable the copy/paste layout and simply activate the upload function ?

If you have already pushed the use of that object, any comments on these two issues ?

Thanks,
Etienne

Leone Scaburri

Dear Etienne,

currently, Smart Import Objects respect only Access and Locking conditions set on Cube visibility rules. Default access permissions (Read / write, Read-only, and No access) are ignored, just like with the Data reader.

Regards,
Leone

------------------------------
Leone Scaburri
Leone
Board International SA
Switzerland
------------------------------
-------------------------------------------

Antonio Speca

Hello Etienne,

Thanks for contacting us, I agree with Leone that you should use the cube visibility to address #1, let me share with you the article.
Cube visibility (boardmanual.com)

Let me give you some context: usually this is what you need in dataview with lock dataentry function. Here you have the same problem to solve, but with the difference that a dataview cell is referenced data, while cells in smartimport are free inputs. So the way to lock some cells of the target cube is the cube cisibility.
let's suppose you have an authorization cube by user and P&L account, 1 represents an editable/visible cell 0 a non editable/non visible cell.
You should set the target cube of the smartimport to be not visible or not editable based on that cube. When the user will try to smartimport a code that is 0 in that cube, then he'll get an error message.
This works also for cubes with different dimensions, just like a "cube cell locked by" in a dataview: missing dimensions will be aggregated, extra dimensions will be repeated.
If in your smartImport you are not uploading the cube with the security, you might create a new cube, assign the same security, and map it to a constant (1 for example) so that this cube will work as "validation" and won't appear in the dataentry form (you might need this trick if you are only uploading relationships and entities.
About #2:
The regional setting that we use are the one in the security profile, you should update the security profile accordingly with the one of the user local for that. 
If you want to force the file upload, unluckily you can't today: the minimum is 1 editable row in the smartimport, probably you can cover the editable part with a label and leave only the fileupload, meanwhile we might think about a nicer way to do this.

Let me know if this is the piece of information you were looking for.


------------------------------
Antonio Speca
Head of Development
Board International SA
Switzerland
------------------------------
-------------------------------------------

Etienne CAUSSE

Hi @Antonio Speca nice to hear from you

I will dig into the "cube visibility" solution for our cases. Basically I understand that the recommended solution would be to use the current "filter cubes" as a "Grant cube" in the cube visibility setup of each DB profile, and that would "natively" prevent a data entry or a smart import on these particular combinations, without touching the access perimeter of the user.

My main concern regarding this feature, is to maintain it over time, as we are currently using specific database profiles for each perimeter and therefore we have more than a hundred DB profiles where we should add specific cube visibility conditions to manage this.

Regarding the Smart Import, if we activate the "Discard Outside Selection" option, as selection also includes basic authorization selection, any record outside of the user perimeter will be discarded also, right ? Even without Cube Visibility rules ? Or should it be an explicit screen selection ? Or should we duplicate the security filter of each DB profile in the cube visibility section ?

Thanks for your clarifications !
Etienne-------------------------------------------

Antonio Speca

Hello Etienne,

It's really not needed to create many db profile, because cube visibility is applied after the selection.

Let me explain.

If you have 2 different users with the same profile but different selections they can have the same profile and accessing different slices of data.
For example let's assume i have a dynamic  security on a user entity so User A accesses the entity member A and User B accesses B.
I want to give different filters  to the two users:
I create a cube structured by the user entity A and B and the other access entity (let's assume it has members X Y and Z)
i set the cube as access cube in the cube visibility and I fill this cube this way
     A    B
X  1
Y         1
Z   1    1

User A will only see the portion A of the setting cube so he will have access to X and Z
User B will see Y and Z

So By using the same profiles and the same filter cubes you ahve today you can manage your use case.

About the discard outside selection: yes, the security is part of the selection so a user cannot load records on which he has no access

If you need further information we can arrange a quick call.

Ciao,

Antonio

------------------------------
Antonio Speca
Head of development
Board International
Switzerland
------------------------------
-------------------------------------------

Etienne CAUSSE

Hi @Antonio Speca, thank you for your feedback.

Our security setup was built with Board 10.1 and therefore without any option than the DB profiles filters.

I understand your recommendation is to restructure our security and reduce the number of DB profiles :
- by moving the current access filters from the DB profile "security filters" to the "select" of the server role ?
- or by integrating these filters in a user-based "grant cube" managed separately ?

If that's the official best practice I'll review this with our admin team. Is there a documentation on this topic to help this kind of migration ? 

Thanks,

Etienne

-------------------------------------------

Antonio Speca

Hello Etienne,

Unluckily I cannot answer Directly, it depends on the application.
If you reduce the maintenance of the profile will be easier, but other than that i do not see many other advantages.
if you plan to use smartimport with the cube visibility locks probably you can set up it on every profile:  it might be a long task, but it might be worth it.

If you want a workaround to avoid this, you might load into staging cubes and then use a dataflow in the trigger procedure to push data to the final cube.

Of course, the cube visibility should be preferred because it will give the user better feedback when he tries to load outside its security and avoids procedures and dataflows.

------------------------------
Antonio Speca
Head of development
Board International
Switzerland
------------------------------
-------------------------------------------

Etienne CAUSSE

Hi @Antonio Speca,
Sorry maybe I was not so clear. I understand we have 3 possible scenarios to manage efficiently smart import rules and combine with the usual security :
Scenario A

1. Create a single standard "USER" DB profile
2. Move the security filters (i.e. the selections specific to each user / team of each country and business unit) in the server role selections and apply the new standard unique DB profile ("USER")
3. Implement all "technical" filters (like P&L line / Nature combination,  authorized products...) through the Cube Visibility rule that need only be maintained in the single "USER" profile.

Scenario B

1. Keep existing DB profiles including security filters
2. Add Cube visibility rules for technical filters in each DB profile

Scenario C

1. Create a unique role at server level
2. Create a single USER DB profile
3. Add two types of Cube Visibility rules :
   1. for technical filters
   2. for security filters using a new "user" entity synchronized with IDP
4. And maintain the security filters within the application itself (would only work if we do it for all data models on the server)

Can you confirm these 3 scenarios would be equivalent in terms of security and functionality ? Any impact on performance ?

Thanks
Etienne-------------------------------------------

Antonio Speca

Hello Etienne,

A and B are equivalent in terms of performance and efficiency.

C is not, nor functionally, nor technically.

I explain myself:

The security selection will limit the members of the entities each user can see in the select window and selectors.
So, let's assume there is an entity named Country containing members France, Argentina and Morocco (not a random choice of the countries )

If i set a security selection on France and then I open a screen with a layout i won't see in the selection Argentina and Morocco, i won't be able to know that those members exist in the db. If I create a report with country by row, the report will only show one row.

On the other hand, setting cube visibility on each cube of the database based on a permission cube having 1 on France, 0 on the other countries, will not limit the selection, so I will be able to see France, Morocco and Argentina in the selection.
If I create a report, i will be able to see 3 rows one for each country, but with only the France Row will show data, the others will show empty cells.
That's why it's called visibility and not security: it won't limit the select like a security filter, but it will decide for each value of the cube if it participates to the layout or not.

The power of this is that you can set more complex permissions, let's add the dimension cost center to our example, I have 2 cost centers: Marketing, R&D and Finance.

If i want to create a user that has access to R&D France and Finance Argentina, and absolutely no access to Morocco then i would configure it this way:
1) security filter on Argentina and France and my own role. Security filter on R&D and Finance
2) an access cube by Role, Country and CC containing 1 on my role Finance Argentina, 1 on my role France R&D 0 elsewhere.
it's impossible for me to exclude directly in selection the combination of France and Finance, because I need the access on the member Finance for Argentina, that's why i creted a cube visibility
IF i create a report  i will only see data for those two combinations, the other combination will be grayed out.

From a performance point of view this implies that for each cell we are going to aggregate in the report, we also check the value(s) of the visibility condition, with a small impact on performance, while the select and security filters has no impact at all.

So: should you put this visibility cube on every single cube of the db?
No, just do this for the cubes that are visible to the final users, and on the ones on which you need to perform smartImport or Dataentry.
Cube visibility is only taken into account for Layout executions, SmartImport and dynamic selections. Dataflows and datareaders will ignore this setting that has been thought to only drive the interactive actions of the final user.

It's better than a filter, because it's calculated at phisycal level, so if the user changes the axis of a report or its configuration cannot break the visibility.

I hope this is clear, otherwise we can take a look together to your specific case, just let me know!




------------------------------
Antonio Speca
Head of development
Board International
Switzerland
------------------------------
-------------------------------------------

Etienne CAUSSE

Thanks @Antonio Speca, that is very clear now.

We will do some testing to better understand the precise behavior of cube visibility versus the previous "layout filter" cubes, but the theory is clear.

Etienne

-------------------------------------------