Thursday, March 29, 2012
Data Space Problem
I have several SQL 2000 databases that displays 0 space
available when I view the properties of the database.
However when I change to the taskpad view, the view
indicates that there is space available.
Is this some kind of bug in SQL Server? Is there any fix
for it?
Any help would be appreciated?
Brennanrun DBCC UPDATEUSAGE and see if that helps.
Andrew J. Kelly SQL MVP
"Brennan" <anonymous@.discussions.microsoft.com> wrote in message
news:59ea01c40090$b4eedc60$a401280a@.phx.gbl...
> Hello:
> I have several SQL 2000 databases that displays 0 space
> available when I view the properties of the database.
> However when I change to the taskpad view, the view
> indicates that there is space available.
> Is this some kind of bug in SQL Server? Is there any fix
> for it?
> Any help would be appreciated?
> Brennan|||Thanks
That worked
Brennan
>--Original Message--
>run DBCC UPDATEUSAGE and see if that helps.
>--
>Andrew J. Kelly SQL MVP
>
>"Brennan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:59ea01c40090$b4eedc60$a401280a@.phx.gbl...
fix
>
>.
>
Data Space Problem
I have several SQL 2000 databases that displays 0 space
available when I view the properties of the database.
However when I change to the taskpad view, the view
indicates that there is space available.
Is this some kind of bug in SQL Server? Is there any fix
for it?
Any help would be appreciated?
Brennanrun DBCC UPDATEUSAGE and see if that helps.
--
Andrew J. Kelly SQL MVP
"Brennan" <anonymous@.discussions.microsoft.com> wrote in message
news:59ea01c40090$b4eedc60$a401280a@.phx.gbl...
> Hello:
> I have several SQL 2000 databases that displays 0 space
> available when I view the properties of the database.
> However when I change to the taskpad view, the view
> indicates that there is space available.
> Is this some kind of bug in SQL Server? Is there any fix
> for it?
> Any help would be appreciated?
> Brennan|||Thanks
That worked
Brennan
>--Original Message--
>run DBCC UPDATEUSAGE and see if that helps.
>--
>Andrew J. Kelly SQL MVP
>
>"Brennan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:59ea01c40090$b4eedc60$a401280a@.phx.gbl...
>> Hello:
>> I have several SQL 2000 databases that displays 0 space
>> available when I view the properties of the database.
>> However when I change to the taskpad view, the view
>> indicates that there is space available.
>> Is this some kind of bug in SQL Server? Is there any
fix
>> for it?
>> Any help would be appreciated?
>> Brennan
>
>.
>
Tuesday, March 27, 2012
Data Source Views - Calculated Columns
Q: How do I use Calculated Columns from a Data Source View in an OLEDB Data Source Adapter.
I took the following steps:
- Created new SSIS project
- Added a Data Source connecting to a SQLServer2005 DB (MyDataSource)
- Added a Data Source View based on MyDataSource (MyDSV)
- Created a Calcualted field to Table Object MyTable (MyCalcField)
- Added a Connection Manager based on MyDSV
- Added Data Flow to Project
- Added OLEDB Source Adapter to Data Flow
- Attempting to Access Calculated Field MyCalcField to be used in Data Flow.
ISSUE: I can't seem to find a way to get the Calculated field to pass through. It's as though this metadata is not available to the Flow.
Anyone have any ideas?
Thanks - MikeyNero
You should not be adding data sources the way you've done it. That's more of a SQL Server Reporting Services thing...
Start a new SSIS project, and add a data flow to the control flow. Then in your data flow, add an OLE DB source. A wizard will come up to walk you through the connection setup.
|||Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.
The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?
Any additonal info is of course welcom. Thanks - MikeyNero
MikeyNero wrote:
Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.
The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?
Any additonal info is of course welcom. Thanks - MikeyNero
Mikey,
I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.
I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.
|||
Hello,
I think I have to agree with Mikey here, there just seem to something missing here. Its almost like MS stop short here. For example the over of usefulness of the data DSV seems to be very limited, let’s say you wanted to do something as simple as created a calculated column. The only thing that provided is a simple text box were a user has to 1 know SQL syntax 2 know the exact column spelling, no object browser, function list or parsing option... (yeah I know it’s dumb but are we not in a drag and drop world?)
I mean they have this logic somewhat existence is SSAS (but seem to have neglected to add it to SSIS) I mean what is so wrong with an object based repository? Some where you can centrally store all business and potential ETL logic that can be universally accessible from both services. (Yeah I know I can bring in an SSAS data source but then have to turn around and build a DSV before i can access any of the objects.) Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation
Cheer
Eric
|||Rafael Salas wrote:
MikeyNero wrote: Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.
The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?
Any additonal info is of course welcom. Thanks - MikeyNero
Mikey,
I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.
I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.
I agree with Rafael. It seems obvious to me that the DSV was designed by the Reporting Services/Analysis Services guys and some person from marketing said "Oooo, wouldn't it be nice if SSIS used that as well - then we can sell this 'common metadata' story"
In practice, using DSVs with SSIS is a pain in the neck. I admit I'm not speaking from experience here but only last week I came across a colleague having problems with them and after that I vowed never to go near them. Emerging best practice for SSIS talks about using configurations - and that seems to fly in the face of using DSVs.
Just my two-penneth worth.
-Jamie
|||
SQLDataMonkey wrote:
Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation
I almost completely agree. I just happen to think that the whole design of this common metadata layer was flawed from the very start. If it had been an architectural design goal rather than an afterthought (which it clearly was) then maybe the implementation of it would have been better. If they were serious about it then it would have influenced the design of SSIS, whereas in practice it seems that the opposite is the case - they had to fit 2 uncomplementary technologies together - and that is never a good idea.
My advice? If you're using SSIS, don't use DSVs!
-Jamie
Data Source Views - Calculated Columns
Q: How do I use Calculated Columns from a Data Source View in an OLEDB Data Source Adapter.
I took the following steps:
- Created new SSIS project
- Added a Data Source connecting to a SQLServer2005 DB (MyDataSource)
- Added a Data Source View based on MyDataSource (MyDSV)
- Created a Calcualted field to Table Object MyTable (MyCalcField)
- Added a Connection Manager based on MyDSV
- Added Data Flow to Project
- Added OLEDB Source Adapter to Data Flow
- Attempting to Access Calculated Field MyCalcField to be used in Data Flow.
ISSUE: I can't seem to find a way to get the Calculated field to pass through. It's as though this metadata is not available to the Flow.
Anyone have any ideas?
Thanks - MikeyNero
You should not be adding data sources the way you've done it. That's more of a SQL Server Reporting Services thing...
Start a new SSIS project, and add a data flow to the control flow. Then in your data flow, add an OLE DB source. A wizard will come up to walk you through the connection setup.
|||Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.
The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?
Any additonal info is of course welcom. Thanks - MikeyNero
MikeyNero wrote:
Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.
The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?
Any additonal info is of course welcom. Thanks - MikeyNero
Mikey,
I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.
I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.
|||
Hello,
I think I have to agree with Mikey here, there just seem to something missing here. Its almost like MS stop short here. For example the over of usefulness of the data DSV seems to be very limited, let’s say you wanted to do something as simple as created a calculated column. The only thing that provided is a simple text box were a user has to 1 know SQL syntax 2 know the exact column spelling, no object browser, function list or parsing option... (yeah I know it’s dumb but are we not in a drag and drop world?)
I mean they have this logic somewhat existence is SSAS (but seem to have neglected to add it to SSIS) I mean what is so wrong with an object based repository? Some where you can centrally store all business and potential ETL logic that can be universally accessible from both services. (Yeah I know I can bring in an SSAS data source but then have to turn around and build a DSV before i can access any of the objects.) Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation
Cheer
Eric
|||Rafael Salas wrote:
MikeyNero wrote: Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.
The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?
Any additonal info is of course welcom. Thanks - MikeyNero
Mikey,
I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.
I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.
I agree with Rafael. It seems obvious to me that the DSV was designed by the Reporting Services/Analysis Services guys and some person from marketing said "Oooo, wouldn't it be nice if SSIS used that as well - then we can sell this 'common metadata' story"
In practice, using DSVs with SSIS is a pain in the neck. I admit I'm not speaking from experience here but only last week I came across a colleague having problems with them and after that I vowed never to go near them. Emerging best practice for SSIS talks about using configurations - and that seems to fly in the face of using DSVs.
Just my two-penneth worth.
-Jamie
|||
SQLDataMonkey wrote:
Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation
I almost completely agree. I just happen to think that the whole design of this common metadata layer was flawed from the very start. If it had been an architectural design goal rather than an afterthought (which it clearly was) then maybe the implementation of it would have been better. If they were serious about it then it would have influenced the design of SSIS, whereas in practice it seems that the opposite is the case - they had to fit 2 uncomplementary technologies together - and that is never a good idea.
My advice? If you're using SSIS, don't use DSVs!
-Jamie
Data Source View of Mining Structure cant be edited?
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,
You can't edit the data source view in the data mniing designer. The DM designer has a "read-only" view of the DSV. You need to switch to the DSV designer to make edits.|||
Hi, Jamie, thank you very much for your guidance.
Sorry for the bad description of the problem. What I originally meant is that: the mining structure cant be edited within the mining structure editor when its data source is OALP. Why is that?
Is it the limitation for OALP data source?
Thank you very much for your further advices.
With best regards,
Yours sincerely,
|||This is a limitation of the OLAP data source. This is not intended for user modification.|||Hi, Jamie, thank you very much.
Regards,
Data Source View and Report Model
Guys,
I created a Data Source View that uses two different Data Source connections to two different databases. I was able to drop in the tables in the data source view designer from the two databases, but when I try to create the Report Model based on that data source view, it's complaining that it can't find table in the non-primary data source from the data source view.
Here is the error I'm getting:
An error occurred while executing a command.
Message: Invalid object name 'dbo.[tablename]'
Command:
SELECT COUNT(*) FROM [dbo].[tablename] t
Why can't it find the database to connect to since the data source view is already creating using two different databases. No problem in the data source view, but when it actually becomes a model, it complains. How come it can't find one of the tables in a different database?
Please help and any help is appreciated.
Enkh.
semantic queries currently do not support multiple data sources. you can create named query that would reference table from another database using db.schema.tablename notation.Data SOurce View and Missing Diagrams
Guys, I am a newbie working through the SSAS tutorial. I am at lesson 5 and something has gone wrong with my Data Source View. Although I have diagrams in the Diagram Source pane I can not get them to show in the actual diagram pane. That pane just shows a grey background rather than the cream canvas. New diagrams in that data source view show the same problem.
If I create a new Data Sorce View, I can create diagrams and see them.
I do not want to create a new Data Sorce View and abandon the old one, because it will break the tutorial. I do not have a record of everything that was in the old one. Neither do I know how to repoint my cube at the new Data Sorce View.
Can someone please tell me how to restore the view of the diagrams.
Thanks
Cliff
How about re-installing the tutorial files? It is hard to tell what was wrong, but you might want to send the broken solution to andrewgaATnetzeroDOTcom and i will take a look.In order to change the data source view for the cube you can invoke View | Code menu item when the cube designer is open, locate the XML code like below and replace the ID of the DSV.
<Source dwd:design-time-name="80d8b583-e073-4437-83cc-f17c461bfa13">
<DataSourceViewID>Simple Sales</DataSourceViewID>
</Source>
Note, that you will probably have to change bindings for existing measures.sql
Thursday, March 22, 2012
Data Relationship & SQL data adapter
I was trying to fill SqlDataAdapter from a " View" with inner join of two tables. When I call the update method of the data adapter it is failing as it cannot generate automatic SQL for multiple tables in my view. Writing own SQL script for update is one way out but it is tedious process.
Is there any other work around to do this?
Can I create a view of two joined data sets on some condition (Just like creating a view in database) in the memory.
Cheers,
Sudath Attanayake.One option you might look into which is extremely common among database programmers, is to only do updates through stored procedures. One of the main reason is security. If you disalow any updates to hapen to the tables or views, then you can control who can update what by simply controlling the permission to execute the individual stored procs, or special case what data can be updated.
Monday, March 19, 2012
Data partition view
I have question regard data partition view .
Please see below sample from BOL + sample of execution plane .
I would like to ask what is the way to avoid the optimizer scan tables out of the scope (I would expect that the only table for this query will be SUPPLY1)
Thanks,
Eyal
--This example uses tables named SUPPLY1, SUPPLY2, SUPPLY3, and SUPPLY4, which correspond to the supplier tables from four offices, located in different countries/regions.
USE tempdb
GO
--create the tables and insert the values
CREATE TABLE SUPPLY1 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150),
supplier CHAR(50)
)
CREATE TABLE SUPPLY2 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 151 and 300),
supplier CHAR(50)
)
CREATE TABLE SUPPLY3 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 301 and 450),
supplier CHAR(50)
)
CREATE TABLE SUPPLY4 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 451 and 600),
supplier CHAR(50)
)
GO
--create the view that combines all supplier tables
CREATE VIEW all_supplier_view
AS
SELECT *
FROM SUPPLY1
UNION ALL
SELECT *
FROM SUPPLY2
UNION ALL
SELECT *
FROM SUPPLY3
UNION ALL
SELECT *
FROM SUPPLY4
GO
INSERT all_supplier_view VALUES ('1', 'CaliforniaCorp')
INSERT all_supplier_view VALUES ('5', 'BraziliaLtd')
INSERT all_supplier_view VALUES ('231', 'FarEast')
INSERT all_supplier_view VALUES ('280', 'NZ')
INSERT all_supplier_view VALUES ('321', 'EuroGroup')
INSERT all_supplier_view VALUES ('442', 'UKArchip')
INSERT all_supplier_view VALUES ('475', 'India')
INSERT all_supplier_view VALUES ('521', 'Afrique')
GO
/* */
SELECT * FROM all_supplier_view WHERE supplyID BETWEEN 1 and 150First
THAT'S A GREAT POST
Second
While you see the optimizer plan, if you look at the number of executes for the other three, you'll see 0. It never did anywork...
And you got Index seeks going on in parallel...
pretty damn effecient, no?
Don't do sample tests in tempdb though, and provide clean up DROPs with the sample...otherwise PERFECT|||Thanks on the compliments.
I have send only sample.
I have almost the same design in a production environment with ~ 1-20 million records per each physical table.
I need to data from only one table with around 3 million recodes in most of the queries.
but I have other table with ~20 million recodes. It means that most on the execution time is useless.
I have done test with direct query to the correct physical table and got the result in 10% of the time!! Vs query the partition view .
I though that partition data view was planed to deal with such cases .
Thanks a lot ,
Eyal|||Are you saying you need to return 3 million records?
I must be misinterpreting...
What does the plan say for the large volume?
Does it show the number of executes other than 0 for the other three?
Are these the only columns in the table?
It doesn't say it, but because the other column is not in the index, it has to go to the data page to get the other column...make a non unique index on the other column...
I'll test it out and see what it does...|||1.I mean that the number of records in the target physical table are ~ 3 melon records vs. ~ 20 million records in other physical table (each physical table means other quarter). The output of the tested query was 7,000 rows.
2.The plans show 1 as number of executes for the other tree tables
3.In my tables there are 5 primary keys ( the DataID is one of it , this one is used as partition constraint) and other 10 measurement columns
eyal|||Well, ok then...
what are you going to do with 7,000 rows? can't be OLTP...are doing batch work against them?|||I insert the result to Daily table group by the extract date and one of the primary keys (means get yesterday records group by on of the rest 4 keys).
This is daily job use to reporting purpose .
Eyal|||I may be wrong, but I don't think you can avoid the reference to other tables participating the view, simply because the view is UNION-based.
Sunday, March 11, 2012
Data not appearing in report at runtime
I am attempting to create and view a simple Crystal Report in VB.net and am running into some difficulty.
- First of all I add my Microsoft Access database as a datasource.
- Then I create a Crystal Report within VB.Net using the fields from my datasource e.g. MyReport.rpt
- Finally I create a form with a Crystal ReportViewer control on it and set the ReportSource to my new report, MyReport.rpt
However when I run my project the report appears but the data does not display.
I have tried looking at a couple of tutorials but haven't been able to figure it out, I'm guessing that I need to run a piece of code at runtime or something for the data to display?
Can anyone please help?
Many thanks in advance.
LeeDid you refresh the report source using your program?|||Hi there,
How do I carry that out?
Many thanks
Lee|||Hi there,
How do I carry that out?
Many thanks
Lee
Just call the method of CrystalReportViewer;
i.e.
CrystalReportViewer CR
CR.RefreshReport()
Thursday, March 8, 2012
Data Model Relationships
several related dimension tables. One of my dimension tables is quite small
and uses a tinyint PK. For some reason when I bring this table into the
model it thinks the PK is a System.Int32 datatype and won't allow a
relationship to the fact table FK which it sees as a System.Byte datatype.
In the database both fields are tinyint datatypes and there is a
relationship between the two.
My question is: Do I have to increase the field to a smallint in order to
create a relationship in the model or is there a workaround. This is my
first attempt at creating a data model for the Report Builder and any tips
would be appreciated. Thanks.I've noticed that if a key is an IDENTITY field then, regardless, it is seen
as an Int32. The behavior seems consistent in reporting services and in
analysis services. What we've decided to do is to make all primary and
foreign keys standard Ints (Int32) instead of tiny and small ints - even when
the smaller values were appropriate. The other thing you could do is to make
the table a named query and cast the key as a smaller int so the relationship
works.
"Elmer Miller" wrote:
> I am trying to create a data source view that includes a Fact table and
> several related dimension tables. One of my dimension tables is quite small
> and uses a tinyint PK. For some reason when I bring this table into the
> model it thinks the PK is a System.Int32 datatype and won't allow a
> relationship to the fact table FK which it sees as a System.Byte datatype.
> In the database both fields are tinyint datatypes and there is a
> relationship between the two.
> My question is: Do I have to increase the field to a smallint in order to
> create a relationship in the model or is there a workaround. This is my
> first attempt at creating a data model for the Report Builder and any tips
> would be appreciated. Thanks.
>
>|||sounds correct to me.
I had an identity on a TinyInt and I think that it just blatantly
friggin crashed SSAS or something ridiculous
-Aaron
Aaron wrote:
> I've noticed that if a key is an IDENTITY field then, regardless, it is seen
> as an Int32. The behavior seems consistent in reporting services and in
> analysis services. What we've decided to do is to make all primary and
> foreign keys standard Ints (Int32) instead of tiny and small ints - even when
> the smaller values were appropriate. The other thing you could do is to make
> the table a named query and cast the key as a smaller int so the relationship
> works.
> "Elmer Miller" wrote:
> > I am trying to create a data source view that includes a Fact table and
> > several related dimension tables. One of my dimension tables is quite small
> > and uses a tinyint PK. For some reason when I bring this table into the
> > model it thinks the PK is a System.Int32 datatype and won't allow a
> > relationship to the fact table FK which it sees as a System.Byte datatype.
> > In the database both fields are tinyint datatypes and there is a
> > relationship between the two.
> > My question is: Do I have to increase the field to a smallint in order to
> > create a relationship in the model or is there a workaround. This is my
> > first attempt at creating a data model for the Report Builder and any tips
> > would be appreciated. Thanks.
> >
> >
> >
Data Model off a view without a primary key
monster, I dont want to make it an indexed view, is there
anyway I can bind to this view without a primary key?
When I try to currently it gives a primary key required
error.
Can I schema bind my view or something? It is a SQL
Server 2005 database.In the model designer you can open data source view editor and assign
primary key to the view.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg" <gfricke@.gmail.com> wrote in message
news:1124216552.895088.277620@.g47g2000cwa.googlegroups.com...
>I am trying to bind the data view off of a view that is a
> monster, I dont want to make it an indexed view, is there
> anyway I can bind to this view without a primary key?
> When I try to currently it gives a primary key required
> error.
> Can I schema bind my view or something? It is a SQL
> Server 2005 database.
>
Data Mining Model Viewer of Decision Tree out of memory error
We've successfully processed a large decision tree model in SQL Server 2005. When I try to view the tree in the mining model viewer, I get the following error:
TITLE: Microsoft Visual Studio
The tree graph cannot be created because of the following error:
'Exception of type 'System.OutOfMemoryException' was thrown.'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Viewers.SR&EvtID=ErrorCreateGraphFailed&LinkId=20476
The link provides no other documentaiton on the error.
We're using 64-bit SQL on a Dell Workstation running XP-64 with 16GB of memory. From my view of things we aren't close to running out of memory. Since the model processed and the error occurs when viewing the model, is this a problem with Visual Studio and nont necessarily Anlaysis Services?
Thanks in advance.
Nick
Hello, NickAs you said, it seems a viewer problem and not a server problem.
There is a simple way to find out: start the Profiler tool (All Programs\SQL Server 2005\Performance Tools\SQL Server Profiler) and connect to the Analysis Services instance.
Then, try to browse the model. If the error is coming from the server, you should see it in the trace.
If possible, please save the trace file and post it (or send it to me directly,
bogdanc AT microsoft DOT com), so that we can trace the problem
thanks|||
Bogdan,
Thanks for looking into this. The model errored I don't see any errors in the trace but I'm not sure. I'm very familiar profiler in term of SQL Server but Analysis Services. I'll send the trace directly to your email.
Nick
|||It looks like the tree has a really large number of nodes (around 34 000) and the viewer has troubles handling that many nodes and runs out of memory. We will definitely try to address this issue in future versions.Meanwhile, here are a few suggestions:
- Increase the COMPLEXITY_PENALTY or the MINIMUM_SUPPORT parameters of the mining model. These would effectively reduce the complexity of the tree, bringing it to a more manageable size.
- Use the "Generic Content Tree Viewer" sample. It can be downloaded from here http://www.sqlserverdatamining.com/DMCommunity/_Downloads/1348.aspx
It allows interactive navigation of the model content and it provides access to all the information inside the tree viewer
- Use the Data Mining add-in for Visio -- it allows rendering of only the top levels of a tree
Friday, February 24, 2012
Data Load Query
I'm extracting data from a mainframe application with a view to loading
it into a MS SQL database. I'm trying to determine the most efficient
way to format the mainframe extract file to make loading into the
database easier.
The problem I have is that the existing record structure includes an
array that can vary between 1 to 50. If I include this array in a
single record the table I use to import the data would need 50 columns
though not all these would be populated. There is a field in the record
to identify how many occurances of the array there are.
Current Record Structure :
Account Number
Account Name
Other Account Details
TotalNumberOfArrayFieldsPopulated
Array :
Value1
Value2
Value3
...
up to Value50 (if required)
i.e.
12344,Mr Agent,$29.95,2,BX123,BX124
12345,Mr Jones,$14.95,3,XX123,XX124,XX125
12345,Mr Jones,$14.00,1,XY123
12345,Mr Jones,$15.95,2,XZ124,XZ125
12346,Mr Smith,$19.95,3,AX123,AX124,AX125
12346,Mr Smith,$19.00,1,BY123
12347,Mr Acant,$99.95,7,CX123,CX124,CX125,CX126,CX127,CX128 ,CX129
There may be up to 3 records created for each Account Number with
different values in the array fields.
Am I better to break this file into two files .. one with the core
customer information and a second file with a row for each array value
which has a link to the customer information file.
Or
Is there a way to efficiently process the original file once it is
loaded into the staging tables in the database ?
i.e.
File 1 - Core Customer Information
====================================
Current Record Structure :
Record Number
Account Number
Account Name
Other Account Details
TotalNumberOfArrayFieldsPopulated
File 2 - Array Information
====================================
Record Number
Array :
Value1
Value2
Value3
...
up to Value50 (if required)
File 1
========================
12344,Mr Agent,$29.95,2
12345,Mr Jones,$14.95,3
12345,Mr Jones,$14.00,1
12345,Mr Jones,$15.95,2
12346,Mr Smith,$19.95,3
12346,Mr Smith,$19.00,1
12347,Mr Acant,$99.95,7
File 2
========================
12344,BX123
12344,BX124
12345,XX123
12345,XX124
12345,XX125
12345,XY123
12345,XZ124
12345,XZ125
12346,AX123
12346,AX124
12346,AX125
12346,BY123
12347,CX123
12347,CX124
12347,CX125
12347,CX126
12347,CX127
12347,CX128
12347,CX129
At times the individual array values will be used for look ups though
essentially the Customer Information record will be the primary lookup
data.
I'm leaning toward changing my COBOL code and creating the 2nd output
unless someone can suggest a simple way to process the information once
loaded into the table.
Any help that could be suggested would be greatly appreciated.For a varying number of fields per record, you might consider XML, as
it's a good format for that kind of data. But I have to say that I have
very limited experience of importing XML data into MSSQL myself - check
out OPENXML in Books Online, the SQLXML tools from Microsoft (which
include an XML bulk load COM component), and you could also post in
microsoft.public.sqlserver.xml to get some feedback on that approach.
Simon