Report Models using Islookup and expandinline

I am building and deploying models.

I have many small description tables, they include two attributes. On attribute is the key (which is a code or type) and the other is a varchar description. They tables are each optionalone related to the primary table. I am "denormalizing" the description tables using Islookup. I change the Role in the primary table to ISLOOKUP, I modify the defaultattributes to remove the code or type key attribute, only the description attribute is now a detailattribute. I hide the key attribute just in case.

In most cases when I do this the description tables are denormalized and the user now sees only the description in the primary table in report builder. But in a few cases the key attribute (which is normally a code - 1, 2, 4, 5, etc) is displayed, it displays even though it is Hidden, ISlookup is defined and I remove it from the default attributes.

I don't know why some work and some don't, the relationships seem to be identical in my view. Is there something in the model that could designate a table can be used as a ISLookup?

To attempt to work around this I have tried using expandinline.

Any ideas?

Report Builder "honors" the Entity.IsLookup property in the UI if and only if the cardinality of the role used to reach the entity is One/OptionalOne, and the lookup entity has exactly one attribute in its IdentifyingAttributes collection.

|||the entity lookup property is one/optional one and there is only one identifying attribute, this is exactly the issue I have, it does not seem to be consistent. And I cannot find any other differences in the tables to help me understand why this is happening.|||There is one other minor constraint - the one identifying attribute must actually belong to the lookup entity (it cannot come from a related entity). Is this the problem?|||No, it does not come from a related table. I used expandinline and it looks good, if I only have the one identifying attribute I seem to get the correct result, anything I should be aware of?|||

If you use ExpandInline, the user will not be able to navigate to the lookup entity using the Advanced Explorer mode in Report Builder. This is useful when creating reports that compare an item to other items with the same lookup value.

It also sounds like you may have confused the DefaultDetailAttributes and IdentifyingAttributes collections on the lookup entity. For an entity to be treated as a lookup, the IdentifyingAttributes collection must have exactly one attribute in it. The contents of DefaultDetailAttributes is irrelevant.

Report Models using Islookup and expandinline

I am building and deploying models.

I have many small description tables, they include two attributes. On attribute is the key (which is a code or type) and the other is a varchar description. They tables are each optionalone related to the primary table. I am "denormalizing" the description tables using Islookup. I change the Role in the primary table to ISLOOKUP, I modify the defaultattributes to remove the code or type key attribute, only the description attribute is now a detailattribute. I hide the key attribute just in case.

In most cases when I do this the description tables are denormalized and the user now sees only the description in the primary table in report builder. But in a few cases the key attribute (which is normally a code - 1, 2, 4, 5, etc) is displayed, it displays even though it is Hidden, ISlookup is defined and I remove it from the default attributes.

I don't know why some work and some don't, the relationships seem to be identical in my view. Is there something in the model that could designate a table can be used as a ISLookup?

To attempt to work around this I have tried using expandinline.

Any ideas?

Report Builder "honors" the Entity.IsLookup property in the UI if and only if the cardinality of the role used to reach the entity is One/OptionalOne, and the lookup entity has exactly one attribute in its IdentifyingAttributes collection.

|||the entity lookup property is one/optional one and there is only one identifying attribute, this is exactly the issue I have, it does not seem to be consistent. And I cannot find any other differences in the tables to help me understand why this is happening.|||There is one other minor constraint - the one identifying attribute must actually belong to the lookup entity (it cannot come from a related entity). Is this the problem?|||No, it does not come from a related table. I used expandinline and it looks good, if I only have the one identifying attribute I seem to get the correct result, anything I should be aware of?|||

If you use ExpandInline, the user will not be able to navigate to the lookup entity using the Advanced Explorer mode in Report Builder. This is useful when creating reports that compare an item to other items with the same lookup value.

It also sounds like you may have confused the DefaultDetailAttributes and IdentifyingAttributes collections on the lookup entity. For an entity to be treated as a lookup, the IdentifyingAttributes collection must have exactly one attribute in it. The contents of DefaultDetailAttributes is irrelevant.

Report Models for OLAP Cubes

Dear all
I studied BOL to find out how Report Models and Report Builder work. In
there it is written that Report Models from Cubes can only be created and
edited inside SQL Server Management Studio or Report Manager but not from
BIDS. They show this as an advantage, because automatical generating saves
time. This is true. But it is also less flexible!
Do you plan to make Cube Report Models accessible in BIDS in the near future
or did I miss a point?
Thanks for your help,
Kind regards,
MarcHello Marc,
You could create a SSAS datasource in the Report Project or Analysis
Project. And then, you could add this existing datasource into your Report
Model Project.
After that, you could create the Report Model in the BIDS.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Wei
Thanks for your answer. I tried this way, but I failed to create the
necessary data source view...
Marc|||Hello Marc,
You could create the data source view in the Analysis Services Project and
use it in the Report Model Project.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei
Thanks for your answer. My problem is still the same - it looks that the
data source view connects always to a relational database - even when I use
the one from an analysis services project.
But I want to create a report model that connects to a cube.
Thanks for your help,
Marc|||Hello Marc,
Sorry for misunderstood.
Your mainly concern is that you need to edit the report model which
generated in Report Manager or Management Studio.
You could do this by following.
After you generated the report model, you could right-click the report
model in the Management Studio and click Edit. It will save a smdl file in
your local machine. And then, you could add this file to your report model
project. You do not need to add any datasource then. You could edit the
report model in your BIDS.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Wei
OK. I created a Report Model Project and added the report model I saved
before out of management studio to my local machine.
After adding BIDS gives me a error message: "...smdl cannot be loaded: The
project must have a data source view associated with the model".
And a second message: Error loading'...smdl': The project must have a data
source view associated with the model.."
But this is exactly what I can not do, because I do not have a data source
view that connects to a cube.
mmmm... Any ideas?
Regards,
Marc|||Hello Roger,
After I save the smdl file to my local machine, I checked the smdl file and
find that it embedded the datasource view.
So when I add the smdl file to the BIDS, it will get the datasource view
information and generate a datasource view.
Please try to open the smdl file you saved and check whether it contained
the datasource information.
If it contains, you could add it to the BIDS.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei
Thanks for this information. Unfortunately I still fail to add the smdl file
to my Report Model Project.
How did you find out that there was an embedded data source view? When I
open the file in Visual Studio, all I receive I see is unformatted xml
text... When I search it for the extension dsv, I do not get a result. But I
think this is the wrong way to look.
Can it be that I need to change a setting in Management Studio that it gives
also the data source view with it?
Best regards,
Marc|||Hi Wei
OK. I checked it to find a enbedded dsv in my report model generated from a
Cube (!) but could not find the data source view within this smdl file.
After that I generated a report model from a relational database (!), edited
this smdl file and added to BIDS like you said. This worked very well.
So it looks that your way works for a relational report model but not for a
analytical (=SSAS, Cube) model.
My goal is to edit a report model generated from a Cube in BIDS...
Any ideas? Thanks Wei!
Marc|||Hi Marc,
I will consult the internal team to check about this issue. I appreciate
your patience.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================.|||Hi Marc,
After consulting our internal team, I confirmed that you could only
generate a model via Report Manager / SSMS.
You may send your feedback to the following url:
http://connect.microsoft.com/sqlserver.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei
Thanks for your support. I will post this feedback at
http://connect.microsoft.com/sqlserver.
Kind Regards,
Marc

Report Models Data Source View

How does the report model know what data source view to use? I could not find it defined anywhere in the .smdl file.

My problem is this. I have a Report Model project with two data sources, two data source views and multiple report models. When I try and bind a data source to an entity in the report model I do not get to choose which data source view to use to choose what table/view I want to bind the entity too and only the tables in one of my DSV's shows up. When I first created it, it worked fine. It automatically selected the correct view and table and was successfully created but now when I open the project, that correlation is lost.

Any suggestions or help is appreciated, thanks.

I found out that if I close my solution and reopen it, the mappings are fixed. Does anyone know if this is a bug or if I could be doing something wrong? I can not find any documentation anywhere describing how data models are mapped to data source views. I really wish you can manually specify what datasource view you want to be using for each model. The autogenerate is nice to initially create the model but if you have already altered the elements or attributes in the model it can be a hassle. Any thoughts?

Report Model is generating without data

I have been generating report models for users to use with Report Builder and there is no data when they select the model. I noticed that the tables I chose did not have a primary key and when I chose a different table, with a primary key, and generated a model from it, then there was data for the user to use in Report Builder.

Is there a documented work around or will I need to set a primary key on each table?

You don't need a physical PK on each of your tables. Instead, open up the DSV in your Report Model project, right click each table, and choose "Set Logical Primary Key" and the appropriate field. You'll then need to rebuild the model. See if this works for you!|||Thanks Russell! That worked just fine after I rebuilt the model.

Report Model in VM environment

What are the limitation when creating report models in VM environment?

Not certain what you mean by "VM environment" but if you are referring to Virtual Machine/Server, yes report models are supported.

Hope that helps.

Thanks, Jon

|||

Jon - Correct, I was refering to "Virtual Machine/Server". When trying to create report models that sits on "Big" tables, it happens very often that I get "Timeout Error" which usually occurs after 5min. All the timeout parameters in Data Source are set to zero but it still happens.

Thanks

|||

I might suggest running with gather statistics=false. This should increase performance of model generation as it will not need to query the data.

Otherwise, in what context is the timeout error occuring. Does the error message denote a SQL or IIS timeout?

Thanks, Jon

|||

Jon - It's a SQL timeout and usually it occurs when column uniqueness is being calculated.

Thanks

Report Model Builder in SQL Server 2005 CTP

Will the model builder have the ability to build models against an Oracle
database? I may be missing something in the CTP but it looks like I can only
make a SQL Server database data source.Directly supported are:
- Analysis Services 2005 data sources
- SQL Server 2000 and 2005 relational data sources
At this point, other data sources (e.g. Oracle) can only be accessed through
the linked server functionality of SQL Server. We are aware that this
approach has restrictions and drawbacks and we are looking into directly
supported additional data sources natively in future releases.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cooper" <Cooper@.discussions.microsoft.com> wrote in message
news:DEEE25FF-79DA-498C-8977-0EF2C631E8E0@.microsoft.com...
> Will the model builder have the ability to build models against an Oracle
> database? I may be missing something in the CTP but it looks like I can
> only
> make a SQL Server database data source.