I use data model as the datasource of my report. I want to create a new field basing on a date type field.
So I create a new field with function like this 'DATEADD(MONTH, 3, #8/18/2009#)', but there is always a error message box said 'the arguments to the following function are not validATEADD'
How should I write this function?
I first got this map from SQL Server Magazine subscription. Now isee it's available to download as a PDF from Microsoft.http://www.microsoft.com/downloads/...&displaylang=enI've just installed Visual Studio Team Edition for Database Professionalsand for the next coming weeks i'll get a chance to see what kind of datamodel design capabilities the product offers.My question would be do you know how I can make our databasemodel look cool like this map? Would you know if this is a third partysoftware that does this with nice colors and all?Do you think I can do similar quality data model map using VSTE forDB Pro?Thank you
View 3 Replies View RelatedI never created table on the basis of Data model diagram . I have to create the 3 table on the basis of given Data model diagram. There are 3 tables
1.md_geographyleveltype
2.md_geographylevel
3.md_geographylevelxref
I have tried to create 2 table but unable to create 3rd table.
CREATE TABLE [dbo].[md_geographylevel](
[type_key] [int] NOT NULL,
[geog_key] [int] NOT NULL,
[Type_description] [nvarchar](50) NULL,
[Store_flag] [nvarchar](10) NULL,
[Type_short_desciption] [nvarchar](50) NULL,
[Code] ....
I am getting difficulties to create table script for table
md_geographylevelxref
How to create the table create script for
md_geographylevelxref
I never created table on the basis of Data model diagram . I have to create the 3 table on the basis of given Data model diagram. There are 3 tables
1.md_geographyleveltype
2.md_geographylevel
3.md_geographylevelxref
I have tried to create 2 table but unable to create 3rd table. Need to review the script of first 2 table and to create table script for 3rd table.
How to create a stored procedure to query the data mining model?
Where can I find some sample code?
Thanks.
Joe.
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.
I am attempting to create the "Classification - Children at Home" Data Mining Model as described in Larson's book.
Each time that I create it.. ONLY the ALL LEVEL is shown and it is impossible to expand the model to look at the Decision Tree, Neural Network, or the Clustering model etc. Drill down is enabled (tried it with and without enabling the drill down).
The Children at Home field has been populated with values from 0 - 4.
Any ideas would be greatly appreciated.
regards Steve
I am attempting to create the "Classification - Children at Home" Data Mining Model as described in Larson's book.
Each time that I create it.. ONLY the ALL LEVEL is shown and it is impossible to expand the model to look at the Decision Tree, Neural Network, or the Clustering model etc. Drill down is enabled (tried it with and without enabling the drill down).
The Children at Home field has been populated with values from 0 - 4.
Any ideas would be greatly appreciated.
regards Steve
Hi,
I am trying to model data in analysis services with the Advance Create Mining Model function in the excel addin. I am having trouble creating an association model that works like the Associate button above the Advanced button.
The format of my data is like this
OrderID Product
100 Bike
100 Helmet
100 Shoes
200 Helmet
200 basketball
200 Bat
300 Shoes
300 Socks
The associate button works perfectly since it asks me which column is the transaction id (orderid) and which column I am trying to predict (product). The advanced create mining model asks me to determine what the columns are...
OrderID=key Product=Input+Predict?
When I run the advance create mining model associate, I get a browser that gives me no rules and the support for only one item itemset (each product but no combination of products).
Does anyone know what I have to do to get it to work like the associate button?
I try to create relational database but i cant develop a model. after i create a database i will create an OLAP cube for my project.i dont want to complex model but i cant create a simple database. it may be marketing, CRM or another. i dont know what fields and tables i should create. i saw sample databases such as northwind and foodmart but i must create original database for my project. please help meeeeee
View 1 Replies View RelatedHi all,
I am using SQL Server BI development studio to create a model. I get the error message when I run the report model wizard:
"Arithmetic overflow error converting expression to data type smalldatetime."
Please help.
Best regards,
i have a problem, because i have 3 tables.
One name "CallPair" contain: id- identity number, from - phone number, to - phone number.
Second name "Talk" contain: id - identity number, callPairId - id from "CallPair" table, date
Third name "Transaction" contain: id - identity number is a number of transaction, idTalk - id from Talk table, callNr - number of talk in transaction
I want mining model to sequence clustering, but i don't know how build case and nested table
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
View 2 Replies View RelatedHello,
What we'd like to do is programmatically generate and maintain a report model based on how the individual install is configured. I've found some documentation that makes me believe this is possible, but I'm hoping someone can nudge me in the right direction. The ReportingServices2005 class has several model related methods (CreateModel) and I found the .xsd for the semantic model XML file
(http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling/SemanticModeling.xsd),
but no real documentation on how to put together a Report Model document from scratch. Looking at the files generated from BIDS is somewhat overwhelming and I have no idea where I'd pull most of the information.
Finally, I want to create Report Model(.smdl) file without Report Model Designer.
I want to Implement CreateModel Method to create new model, any source code sample..??
Any help that can be provided would be greatly appreciated. Thanks!
Sandip.
Hello!
I've created a data source that uses the OLE DB Provider for Teradata to connect to an existing Teradata database. Works fine.
I then use this data source within my project to create a Data Source View. Again, all works well.
However, the final step - creating a Report Model, bombs out in the wizard at the Completing the Wizard step with the following error: "[NCR][Teradata Database] Syntax error, expected something like a name between the 'SET' keyword and the 'TRANSACTION' keyword." (I elected for the wizard to only create entities for all tables, and create attributes).
If I try to create a Model manually, I get the following error when trying to build: "The Entity '<EntityName>' does not have any IdentifyingAttributes. Entity must have at least one IdentifyingAttribute."
Still fairly new to SSRS, so I'm hoping the issue is just a loose nut behind the keyboard Does anyone know what I'm doing wrong in trying to set this up?
Thanks in advance!
Hi,
I've tried those two operations in the Management Studio.
Though we can create a mining structure and mining model in Management Studion,
but we cannot process the analysis-service database.
(1) I create only a mining structure through CREATE MINING STRUCTURE.
No error reported. But if I process the analysis-service database in Management Studio I always get error
'Error : The '<mining_structure__name>' structure does not contain bindings to data
(or contain bindings that are not valid) and cannot be processed.
I then tried to create it by creating and running an XMLA script. It was successful.
However, it's much harder to learn XMLA.
If any of you created an analysis-service database in Mgt Studio, and create a mining
structure in the same place using DMX script, can you process the database?
(2) Is there any use of CREATE MINING STRUCTURE operation without binding
to any table? Examples I saw so far did not show relating it to do. In my experience
processing the analysis-service database with that mining structure is doomed to fail.
(3) Is there any way we can create mining structure through CREATE MINING
STRUCTURE operation in Management Studio and use RELATED TO clause
to bind it to any Relationship to an attribute column (mandatory only if it applies), indicated by
the RELATED TO clause
(4) If this is the fact, is there any use of CREATE MINING STRUCTURE operation?
If we use BI Dev Studio, it's much easier to use the wizard.
(5) I found I cannot create a mining model inside a mining structure through operation
CREATE MINING MODEL. If you call that operation, you end up having a mining
model and a mining structure with the same name. I found that in order to create a
mining model inside a mining structure you have to call operation ALTER MINING
STRUCTURE ADD MINING MODEL. Is it true this is the only way?
Thank you,
Bernaridho
Running 2005 Beta 3 Refresh. When I first deploy, it works fine. Subsequent deployments yield the following error:
View 9 Replies View RelatedHi, I created a Session model via the following
CREATE SESSION MINING MODEL test
(
HCVS_MemberId Text KEY,
HCVS_MeasureDate_Float LONG KEY TIME,
HCVS_MeasureDate Date Discrete,
SysPressure LONG CONTINUOUS PREDICT,
DiaPressure LONG CONTINUOUS PREDICT,
Pluse LONG CONTINUOUS PREDICT
) USING Microsoft_Time_Series ;
How do I specify that I would like to Mean to fill the gap of missing values?
Thanks,
Ricky.
I'm trying to create a few report models in Visual Studio to demonstrate the Report Builder tool and I'm encountering an issue when I try to generate a Report Model.
I get the error message "Specified Method Is Not Supported" when I click Finish on the Report Model wizard. The error detail is:
===================================
Specified method is not supported. (Microsoft Visual Studio)
------------------------------
Program Location:
at Microsoft.ReportingServices.ModelDesigner.Wizards.ChooseSemanticModelName.CreateSqlDsvStatisticsProvider(IDbConnection connection)
at Microsoft.ReportingServices.ModelDesigner.Wizards.ChooseSemanticModelName.BuildModel()
Steps to reproduce:
1. Create a new Report Model project in Visual Studio 2005 against SQL Server 2005
2. Create a new data source. The data source was tested and works correctly
3. Create a new data source view. The data source view was also tested (clicked on Explore Data) and I was able to look at the data in the view
4. Right-click on Report Models and select "Add New Report Model"
5. Click next at the welcome screen
6. Select the data source view created in #3
7. Leave the selected options for report model generation rules for creating the data source view
(Create Entities for all Tables, Create Count Aggregates, Create Attributes, Create Attributes for auto-increment columns, Create date variations, Create numeric aggregates, Create date aggregates, Create roles were all selected)
8. Click next
9. Select Update Model Statistics Before Generating (default option)
10. Click next
11. Click Run
Once I click run it processes for approx 3 seconds and then returns the error above.
If anyone could help me I'd appreciate it. Microsoft hasn't published any details on the namespace and I'm not sure why I'd be getting the error I am.
Thanks in advane,
Maurice
Hi ,
Please tell me how to create Data Source and Data Source View in Report Model Project for OLAP Cube.Whats the provider to use while creating Data Source in Report Model Project to make connection to OLAP Cube.
Thanks
Raghava
Hello,
I'm looking for Programmatically genration of Report Model .smdl file.
Please guide me or suggest some links so that ui can refer same.
Is there any smdl genrator or any classes for the same...?
Thanks in advance.
Sandip
I have made a clean install of SQL 2014 including MDS on a server with Win Server 2012 R2.
After the installation I cant create new models because on an error saying that "Must declare the scalar variable "@Version_Id". "
I can see that the tables gets created in the MDS database but the models dont show up in the web interface.
I get the same error message when I try to deploy an old model.
I have a small VB.Net 2005 app using the Microsoft.SQLServer.ManagedDTS.dll so I can use the SSIS/DTSx functionality.
The program ran fine in the IDE.
Then when I tried to create a setup project for it I recieved a warning that all dependancies for MSXML6.DLL were not found.
I clicked ok and the setup was created.
I tried to run the setup and it failed with "....msxml6.dll failed to register. HRESULT -2147023782. ...
So I said cancel the setup and registered msxml6.dll.
I deleted my setup project and recreated it. This time I did not get the warning about msxml6.dll dependancies.
I tried the setup again and it executed without any errors however when I run the program I now get:
"Cannot create an instance of the XML Document Object Model. MSXML may not be registered."
So I went back into the IDE with the project and now it will not run either. IT gets the same error.
Does anyone have any suggestions?
Hi,
I have found that in the autogenerated model attributes are missing for those fields that have relations to other tables. At first, it may look reasonable since a user can still get down to the field's value through the relation/related table. However, if the relation's key fields is the only thing the user wants to display, then going down to the related table is an overkill.
I can add an attribute manually and bind it to the key field(s). Is there an option in the autogeneration process to do it automatically? The only post I've found so far suggests to do everything manually (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1152575&SiteID=1). Is this the only way?
Thank you,
Leonid
Dear All,
I have a database table which has all the inputs, key and the result. In visual studio, I created a decision tree model which has exactly the same fields as in the table. However the visual studio automatically add space preceding the capital letters. As the field name in the Datamining model and those in the database table are slightly different. I cannot use NATURAL prediction join. Is there anyway to told the visual studio not to add the spaces in the variable names?
Thanks and regards,
Tony Chun Tung Siu
Hi,
Te following situtation is :
ReportModel is created ,there is only a named query in DSV ,it has a few tables in it(The relationship are inner joins and outer joins).
The question is how could I create a unique logical primary key to identify each unique row in the named query dataset, and also you cannt generate a model unless the named query has a logical primary key . how can I solve this problem,any help?
I've been through a number of tutorials on how to enable row-level security based on a userID, but my problem is more complicated and I do not have sufficient understanding of report models to guess.
My security information is defined in a table within my database. It contains a username and an account mask. An account mask maps to 1 or more account codes contained in the other data tables in my report model. A user may have more than one account mask defined for his account.
I understand the concept of directly mapping the logged-in user to a field containing a matching username. Is it possible to do a two-step mapping, so that based on the user ID I can get the account mask(s) and then evaluate which account codes match the mask(s)?
Or is there a different/better way to set this up? Defining SQL roles/groups is not an option, because of some compatibility issues with external systems.
So far, because my security table has no defined relationship with the data tables, I have not even been able to get it into my report model (Would love any suggestions on that one, too.)
I'd appreciate any ideas or suggestions - even if only something to investigate.
Thanks,
Sarah
I have a stored procedure that takes a date range and returns all the sales in that date range. I'm trying to create the report model for ad-hoc reporting. When I go to create the dataset view, it only lets me select tables or views.... how do I get around this?
View 2 Replies View RelatedI've question about how to handle structural datamodel changes in a datasource of PowerPivot. Suppose I'm developing a starmodel in SQL Server and sometimes a datatype changes or a name of a field changes in a table. It seems to me that PowerPivot handle this not gracefully as Analysis MD does (mostly). I received an error because of a wrong fieldname or even no error when a dattype changes in PowerPivot. Is this common or do I something wrong here. Does this mean that every time the datamodel changes the PowerPivot should be recreated? Or am I missing the clue here?
View 6 Replies View RelatedI am getting ErrorCode 8 while loading the data from stage to model. I have checked my error view it states that "Member Code is Inactive".
Initially I have loaded same set of data in Model from MDS Stage table but then deleted with ImportType = 5 which removed all the data from the MDM model.
Now i want to load it back but its giving the Error Code 8 .. Before loading the same data i have changed the stage table Importtype to 2 and Importstatusid to 0.
I don't know if the question has been nailed down. Aside from deleting tables, can we delete the *content* of data within the tables. It doesn't seem crazy that, if you can pull in data from a feed then you should be able to remove the content out again (without also destroying the user's meta-data work ). Reasons for this include:
- Security (a user may not have rights to see *my* data and should go refresh their own)
- Size (workbook doesn't need to have GB's of irrelevant data saved to disk in a workbook if it was just useful during development phase to a pre-production data feed)
- Bad data (pre-production data feed is not good data)
- User-friendliness (data feed was refreshed 2 years ago and workbook was saved to file server. Users shouldn't be presented with irrelevant data, but should get empty pivot tables until they go do their refresh)
Obviously Excel internally knows how to clear out PowerPivot data, given the prompt shown here: [URL] ....
But how does a user initiate this on their own (corruption aside)?
Previous time this question was asked, without a real resolution: [URL] ....
Hi,
I've created dsv that contain all fields from table database. in the smdl I've remove some fileds due to security. All fields in the smdl do not contain drill.
Issue: When I created calculated field in the report builder the field has a link. When I clicked the drill I saw all the record data including field that not in the smdl.
Questions:
1) Can I remove the link from the calculated fields?
2) Can I prevent from users drill to fields that not in the smdl?
Thanks,
Assaf
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies View Related