Hi,
Is it possible to do a cross database join in a report services data source view? It doesn't look like it.
If not I was thinking of linking the table into the other database.
TIA,
Darren
Hey All...
Got a View question.
Have 2 tables:
#1 Currencies
|CCY_Name|CCY_Code|
#2 Rates
|CCY1|CCY2|CCY3|...etc|Active|
-> where the Columns CCY# = the Records in #1
How do I build a View to Select the ONE record in #2 where Active=Y, having the CCY_Name from #1 based on #2.CCY1 (Column NAME) = #1.CCY_Code (Record).
Thanks
robbied111
Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
I was trying to load data using SSIS, Data Flow Task, OLE DB Source, source was a view to a OLE DB Destination (SQL Server). This view returns 420,591 rows from Query Analyzer in 21 seconds. Row length is 925. When I try to executed the Data Flow Task from SSIS, I had to stop the process after 30 minutes, because only 2,000 rows had been retrieved. I modified the view to retun top 440, 000 and reran. This time all 420, 591 rows were retrieved and written in 22 seconds. Next, I tried to use a TOP 100 Percent. Again, only 2,000 rows were return after 30 minutes. TempDB is on a separate SAN Raid group with 200 gig free, Databases on a separate drive with 200 gig free. Server has 13 gig of memory and no other processes were executing.
The only way I could populate the table was by using an Execute SQL Task and hard code an Insert into table selecting data from the view (35 seconds) from SSIS.
Have anyone else experience this or a similar issue? Anyone have a solutionexplanation?
,
Hi
In this code how can I create a new data source and new data source view and model and structure that it run dynamic.
In this code I have a lot of errors, that they are about server and database don€™t have in current code,
In this code, first I should definition server or no?
Database dbNew = new Database (databaseName,
Utils.GetSyntacticallyValidID(databaseName, typeof(Database)));
srv.Databases.Add(dbNew);
dbNew.Update(true);
***********************************************************
How can I create data source and data source view and model and structure?
Please say code of that, and guide me.
databasename and srv is unknown.
Do I add other reference with analysis services?
Please explain about these codes:
************************************************************************
1)
RelationalDataSource dsNew = new RelationalDataSource(
datasourceName,
Utils.GetSyntacticallyValidID(
datasourceName,
typeof(RelationalDataSource)));
db.DataSources.Add(dsNew);
dsNew.ConnectionString = connectionString;
dsNew.Update();
2)
RelationalDataSourceView rdsv;
rdsv = db.DataSourceViews.Add(
datasourceviewName,
Utils.GetSyntacticallyValidID(
datasourceviewName,
typeof(RelationalDataSourceView)));
rdsv.DataSourceID = ds.ID
***************************************************************
3)
OleDbConnection cn = new OleDbConnection(ds.ConnectionString);
OleDbCommand cmd = new OleDbCommand(
"SELECT * FROM [" + tableName + "] WHERE 0=1", cn);
OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
DataSet dss = new DataSet();
ad.FillSchema(dss, SchemaType.Source);
*************************************************************
4)
// Make sure we have the name we thought
dss.Tables[0].TableName = tableName;
// Clone here - the original DataTable already belongs to a DataSet
rdsv.Schema.Tables.Add(dss.Tables[tableName].Clone());
rdsv.Update();
5)
MiningStructure ms = db.MiningStructures.Add(miningstructureName, Utils.GetSyntacticallyValidID(miningstructureName,
typeof(MiningStructure)));
ms.Source = new DataSourceViewBinding(dsv.ID);
ms.CaseTableName = "Customer";
Add columns:
ScalarMiningStructureColumn smsc;
// From table "Customer" we will add a couple of columns
// CustomerID - key
smsc = new ScalarMiningStructureColumn("Customer ID",
Utils.GetSyntacticallyValidID("Customer ID", typeof(ScalarMiningStructureColumn)));
smsc.IsKey = true;
smsc.Content = "Key";
smsc.KeyColumns.Add("Customer", "customer_id", OleDbType.Integer);
ms.Columns.Add(smsc);
*******************************************
6)
MiningModel mm = ms.MiningModels.Add(miningmodelName,
Utils.GetSyntacticallyValidID(miningmodelName,
typeof(MiningModel)));
mm.Algorithm = "Microsoft_Decision_Trees";
mm.Parameters.Add("COMPLEXITY_PENALTY", 0.3);
MiningModelColumn mc = new MiningModelColumn("Customer ID",
Utils.GetSyntacticallyValidID("CustomerID",
typeof(MiningModelColumn)));
mc.SourceColumnID = ms.Columns["Customer ID"].ID;
mc.Usage = "Key";
mm.Columns.Add(mc);
mm.Update();
Please exactly say, whatever I want
Thanks a lot for your answer
Please don€™t move this question because I don€™t know where I should write this.
hi everybody,
i want to create data source and data source view for data mining, with using C Sharp.
i have create data source and data source view and export to XML file, but when i change to another computer, run those XML file, it return error, when i run statement to create and biuld mining model, what can i change on xml or how to run XML on another computer sucessfully,
and have i build data source and data source view, how to do it.?
thanks for you helps
I have a fact table which is a custim query in the data source view, I process and work with the data, after somewhile I make some changes in the data source view query but all my changes does not reflect in the cube after processing the same old data returned.
please help ASAP, I want to fix this issue
Is it possible to use a stored procedure in a Data Source View? When I use the wizard to create the Datasource View, all it lets me choose is tables and views in the database. Am I missing something, or trying to do something that is impossible?
View 8 Replies View RelatedHow 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.
Is there a limit on the number of fields that can be displayed in a table object in a data source view in SSAS 05?
One of the tables (the fact table) in my data source view is displaying only 50 fields. The table actually has many more than that. One of the fields that is not displaying is a foreign key that I need to link to a new dimension table. I have tried refreshing the view, but it doesn't bring in any additional fields.
only the tables and views are shown in the wizard (BIDS) thanks...
View 1 Replies View RelatedI would like to use a dynamic pivot table in my data source view. It seems that a named query can be only one sql statement. So, I cannot use my multi-statement procedure that creates a dynamic pivot table output.
What is the best course of action here? I could hard-code my pivot table query. I could maintain a redundant table in the pivot format. Do I have any good options?
KenS
Ken
We've built an oltp that uses nothing but synonyms that reach out to various DB's in the organization thru linked servers.
The issue that we are having is Data Source Views can only see tables and views. Is it possible to use synonyms? If not what would be a work around.
Please advise. Thanks
Steve
Hi, all here,
Thank you very much for your kind attention.
I got a quite a strange problem with Mining structure for OLAP data source though. The problem is: I am not able to edit the mining structure in the mining structure editor. The whole data source view within the mining structure editor is greyed. Could please anyone here give me any advices for that?
Thank you very much in advance for any help for that.
With best regards,
Yours sincerely,
I have an existing project with the data source and report folder with working reports. I need to create a model for other department members to use Report Builder, not Business Intelligence Developer Studio, to develop their reports from. How do I add a data source view and report model folder to the project so I can then create a view and model?
1. As per my current development SQL Sever Analysis Database consists of two Cube (Cube A and Cube B).  Cube A and Cube B share the same data source view (DSV1). The source for both these cubes has the same data source (DS1).
2. As per the requirement I need to create third Cube i.e. Cube C. Is it possible to create a second Data Source View (DSV2). The Source of second Data Source View (DSV2) will be the same data source(DS1).
I am thinking to create second Data Source View (DSV2) for Cube C because existing layout of the DSV1 has become complex. I wanted to know the pros and cons of creating a multiple data source view with same data source
hello ,
I want to download data from an AS/400 to a SQL 2005 Database.
Within Sql server 2000 it was possible to do more than 1 transformation with just 1 source connenction and 1 destination.
In SSIS I have created:
- new solution
- data source
- Data source viewer ( I made named queries to format the data as i want it to be in Sql)
Although it is possible to view the named query data it does not seem to be possible to import the data in the data viewer into my sql 2005 database. (For each dataflow I have to define each query again)
I have added the data source in the connection manager.
Also I am searching to import more than 1 file in 1 package !
I can use however a Dts-2000 package but why then use SQL server 2005
Hope somebody can help me
Regard,
Ronny
I created a data model for report builder. And since it wont let me use views, i tried to put all the tables im using in one of my already made views, to create a data source view like my sql view. But when i connect everything the same, my report model still doesnt narrow my search. I only want to see Breed information, but since i have a Breeder table,and Customer table, its showing me all customers, and not limiting it to the ones that are breeder customers.
heres the tables:
Code Snippet
FROM dbo.Tbl_Customer INNER JOIN
dbo.Tbl_Customer_Breeder ON dbo.Tbl_Customer.Customer_Code = dbo.Tbl_Customer_Breeder.Customer_Code INNER JOIN
dbo.Tbl_Customer_Detail ON dbo.Tbl_Customer.Customer_Code = dbo.Tbl_Customer_Detail.Customer_Code INNER JOIN
dbo.Tbl_Customer_Category ON dbo.Tbl_Customer.Customer_Category_Id = dbo.Tbl_Customer_Category.Customer_Category_Id INNER JOIN
dbo.Tbl_Customer_Classification ON
dbo.Tbl_Customer.Customer_Classification_Id = dbo.Tbl_Customer_Classification.Customer_Classification_Id INNER JOIN
dbo.Tbl_Customer_In_Breed ON dbo.Tbl_Customer.Customer_Code = dbo.Tbl_Customer_In_Breed.Customer_Code INNER JOIN
dbo.Tbl_Breed ON dbo.Tbl_Customer_In_Breed.Breed_Id = dbo.Tbl_Breed.Breed_Id
What am i doing wrong, and are there any suggestions.
We have a relational database (rd) and a data warehouse (dw). This dw has a table (tw) with all key fields (dimension keys) and metric related (measures) fields. This table is populated with monthly data each month. The tw is joined to various look up views present in the dw to obtain name fields from rd. The DBA wants me to remove the look up views. I now have following 2 options that I can think of –
1)Further de-normalize the tw and store the name fields as well. However, there are two issues with this option –
a.The size of tw will grow tremendously.
b.We are storing monthly data and the values in the name fields may change after some time. Then we will have to put in additional views/objects to obtain the latest name.
2)Using ETL, obtain the copy of rd tables overnight in dw. We will then join tw with these tables and there will no longer be cross database joins. However, this will be a burden on maintenance and support.
As of now these are the possible options I can come up with. Which one would you suggest and why? If you have another option, please let me know.
Thank you all in advance,
sajmera
I have a datasource view DSV1. It points to a datasource DS1 that is considered the "primary".
I have created a Report Model that uses DSV1 (and thus uses DS1)
I created a new datasource, DS2 that I would like to use instead of DS1. (I can't just modify DS1 because if I modify it, it will overwrite it when we go to our Production environment and break that datasource)
So, I can go into DSV1 and change all the references from DS1 to DS2.
But that's where the problem lies.
When I try to build, I get the following error:
"The Table property of the Entity "E1" refers to the Table "dbo_View", which is not in the primary data source."
Somehow, the entity is tied to the "primary" datasource. When I change it back to DS1, everything works fine. Any thoughts? What can I do?
Hi everyone!,
Our team is new to SSRS and we are trying to figure out how to refresh the data source view, autogenerate the corressponding report model and Re-Deploy the Report Model (rewrite existing Report Model) on the server on a periodoc basis or trigger the whole action whenever there is a change in the database on some specific tables.
We prefer to do this action through a Agent job ( a diff approach is also welcome).
Are there any stored procs out there?
Appreciate your time.
-PN
Thanks for any one can give me a help.
I am try to transfer some tables data from one database server into another database server. I create a package in SSIS, and I use a variable to pass each table name. In Data flow, I use a OLEDB Source, but I cannot set the Data access mode to Table name or view name variable. Ever time, I will get this following error info "===================================
Error at Data Flow Task [OLE DB Source [31]]: A destination table name has not been provided.
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)".
Some one can tell me what is the reason, or give me some examples.
Thanks in advance.
good morning
I wanna to put a sub query in the cross join like that
SELECT *
FROM table1
CROSS JOIN (select field
from table2
cross join table3)
but i got this error
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.
can anyone help me!!!!
How to set default values for selection list navigator without using parametrized queries for data source view.
View 2 Replies View RelatedI have the following structure with remote select permissions; I cannot create temp tables or use stored procs:
tblEvent with event_pk, eventName
tblReg with reg_pk, event_fk, person_fk, organization_fk
I'm currently using a case statement to get counts for these categories:
case
when c.person_fk is Null and c.organization_fk is not null then 'Employer'
when c.person_fk is Not Null and c.organization_fk is null then 'Individual'
when c.person_fk is not Null and c.organization_fk is not null then 'Both'
else 'Unknown'
end
But I need some kind of count (0) for every category. I've used a cross-join, group by in the past - but what do you do if you don't have a table? For example, the end result when selecting event_pk=(112,113) would be:
event_pk, myCount, countCat
112 0 Employer
112 1 Individual
112 4 Both
112 0 Unknown
113 5 Employer
113 0 Individual
113 0 Both
113 2 Unknown
Thanks for any help,
jb
I am wanting to show Total Quantity of Presentations2 for each EmpVol and TypeofPresentation, even if there are no Presentations done in TypeofPresentation or if an Employee did not do any Presentations.
I am close with the query below - but not getting back exactly what I want - Ideas.
SELECT ISNULL(Presentations2.Quantity, 0) AS Quantity, TypeofPresentation.Typeofpresforrpt, EmpVol_2.LastName, EmpVol_2.FSSTVOLUNTEER
FROM Presentations2 INNER JOIN
EmpVol ON Presentations2.ID = EmpVol.ID RIGHT OUTER JOIN
TypeofPresentation ON Presentations2.TypePresINT = TypeofPresentation.TypePresINT CROSS JOIN
EmpVol AS EmpVol_1 CROSS JOIN
EmpVol AS EmpVol_2
GROUP BY TypeofPresentation.Typeofpresforrpt, EmpVol_2.LastName, EmpVol_2.FSSTVOLUNTEER, ISNULL(Presentations2.Quantity, 0)
HAVING (EmpVol_2.FSSTVOLUNTEER = N'FSST')
Thanks,
I have the following 2 tables:(BATCHES)BatchID [int] KEYID [int]OrderID [int]Action1DateTime [datetime]Action2DateTime [datetime]Action3DateTime [datetime]Action4DateTime [datetime]Action5DateTime [datetime]Action6DateTime [datetime]Action7DateTime [datetime]Action8DateTime [datetime](ORDERS)OrderID [int] KEYProductionLineID [int]RecipeID [int]OrderAmount [int]Batches.Action1DateTime to Batches.Action8DateTime can have several entrieseach day.I need a query to count all Batches.Action1DateTime to allBatches.Action8DateTime for each day in a specified period.I also need to specifically use where clauses for Orders.OrderID and/orOrders.RecipeID.I need the data to draw a graph for each ActionXDateTime as a function ofdate.Any help appreciated./Henrik
View 3 Replies View RelatedHello
I have to retrieve a cross join between languages and language-skills for more person in a matrix (grid) report.
E.g. i like to ritrieve the TOP 2 people in each "cell" of the grid (order by name).
There are 3 people in cell "ITALIAN / very good".
So i have to retrieve only the top 2.
In table tEmployee i have the people names (john, anna, michael)
In table tLanguage i have all language (english, italian, german)
And in table tLanSkill i have the language rating (bad, good, very good)
bad
good
very good
Englisch
Michael
John
Anna
Italian
John
Michael
Anna
German
Anna
THX for your help.
ciao, michele
How can I achieve the same effect as a cross join (since the merge operator doesn't have a cross join)?
Situation is this... a flat file has some header and footer information that I need to keep and attach to each row. So for simplicity sake of an example lets just say header has only 1 thing we care about - a row that says DATE=01/01/06.
I take the file and run a split to split into "Date" "Data" and "other" (other has all the throwaway rows in header and footer I don't care about). Then I use a derived column object to get all the columns out of the "Data". Finally I want to add that Date metadata back to every row in the data...
I thought this would be an easy thing to do.. but I can't seem to figure out how to duplicate that Date info into every row.. Hopefully I am overlooking something simple.
Thanks in advance.
StaffTable
StaffID (int ,PK)
StaffFullName(vnchar(50))
DepartmentTable
DepartmentID (int,PK)
StaffID (int,FK)
DepartmentName(vnchar(50))
it seems when I use SELECT * FROM StaffTable,DepartmentTable
it returns a lot of wrong data.(I guess, the right data is just in 10%, 90% is wrong)
So , is this mean, in a real industry software project, no one would use cross join?
Hi folks, this is my first time here... i hope that you can help me with this query
I have two query and one table
Queries
Q1-Training - List of training
Q2-Group - Groups that must take the training
Table
T1 - Training_Group
Here a query example using SELECT * FROM
Q1
Code Name
-----------------------------------
AD0041 Advance SQL
BE0012 Beginner SQL
NE0001 Newbie SQL
...
Q2
Group_Name Group_Code
-----------------------------------
Administration ADMIN
Directors 456
John Smith 555666
...
T1
Code Level Group_Code
-----------------------------------
AD0041 1 ADMIN
AD0041 2 456
AD0041 3 555666
BE0012 2 456
NE0001 1 ADMIN
Here's my problem :
I need to make a report that look like that
(as training) (Group_Code) --> dynamic column
^^^^^^ ^^^^^^
Training ADMIN 456 555666
-------------------------------------
AD0041 1 1 1
BE0012 0 1 0
NE0001 1 0 0
I did it in ACCESS with TRANSFORM and PIVOT.
But how i do that in SQL Server 8.0 ?^
Thanks in advance for your solution or hints... i'll be back tomorrow morning
Best regards
David
I have the following tables:members--------------member_idmember_tpc_id ( = tpc.tpc_id)tpc------tpc_idcourse------------course_idtpc_assignment---------------------------tpc_assignment_idcourse_idenrollment-------------------member_idcourse_idenrollment_status Now I want to select all members where member_tpc_id>0 and get the enrollment_status of each member in each course where course_id IN (Select course_id From tpc_assignment)Now what i did was get all the members and then all the courses and did a cross join between them. There are about 1900 members and 80 courses and when I do a cross join I get 1900*80 rows (152000) and the status of each member for all the 80 courses. If not enrolled it returns Not Enrolled (i have a UDF which takes a member_id and a course_id and returns the status). The BIG problem is that its taking about 6-8 mins to run the query and as a result its timing out on the aspx page. Can someone please tell me how I can do what i am trying to do without using the cross join because I suspect its the culprit here. The query I came up with is Select
*, dbo.returnStatus(temp1.user_id, temp2.course_id) As Status,
(Select tpc_title From tpc Where tpc_id = temp1.member_tpc_id) As Tpc_Title
From
(Select member_id As user_id, member_name, member_tpc_id
From members Where member_tpc_id> 0 And organization_id = '1'
)temp1
cross join
(Select course_id As course_id, course_title As course_title
From course Where course_id IN
(Select course_id From tpc_assignment Where tpc_requirement_id IN
(Select tpc_requirement_id From tpc_requirement) And course_id<>0 And organization_id = '1')
)temp2
Order By member_name, Tpc_TitlePlease help. Thank you.
Hello,
I have a query that returned much more results than it should. I believe this is because it was a cross join instead of inner join (because I forgot to map a temporary relationship in the designer of SQL Server Express; I think this is what the problem may have been, but I can't remember), but could it be because the join criteria is because it is an nvarchar(MAX)?
I did the join on two nvarchar(MAX) fields. The table has 31102 rows in both tables, and it would pull back the first entry in the first table, and all 31102 rows in the second table, and so it would pull back 31102 rows for each entry in the first table.
Is that only because of the cross join, or maybe because of nvarchar(max)?