I create the cube and KPI successfully.Now want to define the "trend" in MDX.the problem is the trend is not linear, but a quadradic function, which means the kpi target is the highest point in the function.in order to get the "trend expression", to get the "absolute value" is needed (as to measure how near to the target point).but i browsed the operators available, there is no ||.
View 8 RepliesDoes anyone know how I can get just one record returned from a select statement - not by doing set rowcount, but saying I want, for example the third record returned.
I'm trying to do a stored procedure that does a select of current records that will reduce the output to roughly 24 records and then get a random record from this - I need to do it all in a stored procedure.
What I have so far is :
BEGIN
DECLARE @Count int
DECLARE @RowCount int
DECLARE @IDint
/* Randomise the Data */
SELECT @Count = Count(*) FROM MyTable WHERE fRotation = 1
SELECT @ID = Rand() * @Count
END
As you can see, @ID will hold a record no.
I cant use batch cursors as they are giving me problems - work for a short while and then they mess up.
Any ideas anyone?
Thanks
Rich Hopwood
I'm almost embarassed to ask such a simple question but I'm pretty new to SQL and want to the do the following SQL select. I have two tables:
WORK ORDER HEADER DETAILS (WORKH)
WorkOrderNumber | CustomerNumber | StartDate | EndDate
WORK ORDER LINES (WORKD)
WorkOrderNo | Service
I want to select all customer numbers that have a work order that includes a line with the service code XXXXX and a start date of YYYYYY and end date of ZZZZZZ. Note that the first field in each table contains the same data but is named differently. It's this that's causing me a headache.
Thanks in advance.
BB
I just downloaded and installed Visual Basic Express Edition and SQL Server Express Edition. All I have in the Start menu is SQL Configuration Tools. My absolutely stupid question is, how do I get started with this?
I know a little bit about SQL from using SQL command line in Oracle, and I've built small databases before using Access. Where does this program fit into all of that? And what do I use and how can I get started with this program?
Seems to me like microsoft mentions learning, but it's never for beginners. I haven't found a single tutorial that says how to make a simple database step by step.
Does anybody know about a tutorial focused on teaching the very basics (a kind of "hello world" application) from setting up the new database with a few sample fields to "deploying" it (making it ready to be used)?
Please let me know.
Once I'm through with these first steps there seems to be a lot of helpful learning resources meant for experienced and expert users, but I'm not there yet...
Thanks,
Bernardo
Hi all,
I'm a user interface designer working for a small development shop in a government agency. My boss feels it would help our team out if I learned a bit more programming (I'm on that already in VS2k8, VB.NET), and some general database theory and practical knowledge. The more I learn about programming, the more my utter ignorance of databases proves to be an obstacle. We use SQL2k5, so I've decided to cut my teeth on that; I don't have the free time to learn something simpler and then graduate to SQL, so it is what it is.
So, I'm looking for a "Dumb Even for a Dummy" guide to database fundamentals, with a strong emphasis on SQL in particular. I slogged through the first 30 pages of this forum and found a couple of MSDN resources but nothing stuck me as being basic enough. I'm hoping someone can provide me with some "Go here, newb" resources to get me started.
Thanks in advance!
Hi everyone.
Using C# 2005 EE with SQL 2005 EE
I am really new to SQL, got qns here.
Can my program with SQL database run correctly on another com using win xp home edition, with only C# 2005 EE installed?
Is it compulsory for every table in a database to have a primary key?
Can I extract a data from a specific cell in a database table(click button get data)? How do I go about doing it (the simplest n straight forward method)?
Many Thanks in advance.
Regards,
dragoncells.
I have a table that expands and pushes down objects under it, i don't want this to happen i want my objects to use an absolute position so i can jiggle things how i want them - this doesn't seem to be an option in SSRS 2005?
View 2 Replies View Relatedwhen i put a table beside 2 graphs on my report, as the table expands it pushs the bottom table down ONLY when when it's displayed on the final webpage. the preview features shows it as being correct.
how do i prevent the expanding table from pushing the tables BESIDE it down? i could understand if it was a table below it, but it's not.
Hello there...
Pending receiving the full version from the higher powers at my work, I have downloaded the Evaluation version of SQL 2000, and set up an instance on my desktop machine. This was set up on an administrator account, and works fine.
My main aim with SQL 2000 is to upsize all the companies piddling little Access 97 databases into an SQL backend and keep the 97 frontends. I have migrated some of the tablesets into SQL, and linked to them in Access 97 and they work fine.
However, the problems are when I try and set up ODBC. When I enter ODBC as a different user on the server itself, the ODBC list of available SQL Servers shows me the correct server information - my PC ID no, followed by the server name. However, when I try and connect with another machine on the network, it only displays the PC ID no of the server, and not the server name. When I attempt to add the server with just the PC name, it doesnt find the server - same when I add the name of the database to the end of the PC ID no.
I'm sure there is something really basic I have not done, but I'm totally stumped now. I am using Windows Auth, and have set up the login of the other PC I am trying to connect, as an authorised user.
Please help me - I am just a doofus, please feel free to laugh at me...
Hi all,
I have a strange situation. I have a field in the database that has to be a string type field of around 4000 characters.
So naturally I setup the field as
type: varchar
length: 4000
However when I try to put any text in this field I find that I can put no more than 1023 characters of ascii text in there.
To check if this was a max record length prob I setup a test table with only 2 fields:
ID: int, PK, Identity
longVarchar: varchar, 4000
and tried to put some ascii text into the field called longVarchar. Again the most I could put in was 1023 characters!
Thinking that it could just be that SQL svr box that was wacky, I tried it on another one with the same result.
I have tried using other field types (nvarchar, char) and have found that they all could only hold 1023 characters max, no matter what how high I defined the size of the field.
Try it out yourselves and see if you get the same result. Any useful suggestions would really be appreciated.
In SQL SERVER 2000,I had built a function that retrieved absolute day, month, and year between two dates.
View 6 Replies View RelatedHave a bit of an SQL challenge in that I need to create a well performing view in SQL2008R2 which calculates hours remaining at any given point in time. This is not a simple sum but has some absolute values included.
---------------------------------------------------------------------------------------------------
-- Basic simplified structure of maintenance and usage tables
---------------------------------------------------------------------------------------------------
CREATE TABLE #Maintenance (Id int NOT NULL
IDENTITY(1, 1)
CONSTRAINT PK_Maintenance PRIMARY KEY,
MaintenanceDate smalldatetime NOT NULL, -- When maintenance hours are reset
HoursAvailable numeric(5, 1) NULL -- Available hours until next maintenance
)
[Code] ....
---------------------------------------------------------------------------------------------------
-- Required output view so that at any given point in time I can determine hours remaining
---------------------------------------------------------------------------------------------------
-- AtDate HoursAvailable
-- '01-Jan-2015 15:00' NULL -- before first maintenance hence unknown
-- '10-Jan-2015 10:00' 10.1 -- maintenance hours reset
-- '11-Jan-2015 07:30' 8.9 -- subtract 1.2
-- '11-Jan-2015 11:10' 2.9 -- subtract 6.0
-- '15-Jan-2015 00:00' -0.1 -- subtract 3.0
-- '01-Feb-2015 00:00' 12.2 -- maintenance hours reset
-- '02-Feb-2015 13:00' 10.0 -- subtract 2.2
hi
i am learning SQL basic commands ( like , where, having, group by, order by , join ,and simple sub-query) and I got one exersice I cannot solve
table RECIPE has 3 columns , ( pizza ,ingredient,amount)
each pizza could use 1 / many ingredient, each ingredient could be used by 0 / 1/ many pizza, amount means the quantity of that ingredient used in certain pizza
question:
(List the ingredients, and for each ingredient, also list the pizza that contains the largest amount of this ingredient.)
============================
Raw data :
recipe
======
pizza ingredient amount
--------------------------------
margarita spice 5
margarita cheese 120
ham ham 150
ham spice 5
napolitana anchovies 100
napolitana olives 75
napolitana spice 10
hawaiian ham 100
hawaiian pineapple 100
hawaiian spice 5
cabanossi cabanossi 150
cabanossi spice 10
siciliano onion 50
siciliano capsicum 75
siciliano olives 50
siciliano anchovies 50
siciliano spice 15
americano salami 120
americano pepperoni 75
americano spice 10
mexicano onion 75
mexicano capsicum 75
mexicano mushroom 50
mexicano chilli 25
mexicano spice 20
seafood seafood 200
seafood spice 5
garlic garlic 25
garlic spice 10
vegetarian onion 50
vegetarian capsicum 50
vegetarian mushroom 50
vegetarian peas 50
vegetarian tomato 50
vegetarian spice 5
mushroom mushroom 100
mushroom spice 5
special cheese 25
special tomato 25
special ham 25
special anchovies 25
special olives 25
special mushroom 25
special bacon 25
special egg 25
special pineapple 25
special cabanossi 25
special salami 25
special capsicum 25
special onion 25
special peas 25
special seafood 25
special spice 10
stagiony ham 75
stagiony mushroom 50
stagiony olives 50
stagiony anchovies 25
stagiony spice 10
====================================================
Query Results needed:
ingredient pizza amount
------------------------------
anchovies napolitana 100
bacon special 25
cabanossi cabanossi 150
capsicum mexicano 75
capsicum siciliano 75
cheese margarita 120
chilli mexicano 25
egg special 25
garlic garlic 25
ham ham 150
mushroom mushroom 100
olives napolitana 75
onion mexicano 75
peas vegetarian 50
pepperoni americano 75
pineapple hawaiian 100
salami americano 120
seafood seafood 200
spice mexicano 20
tomato vegetarian 50
=============================================
Thanks
Hello,
I have a report that prints cards for customers. The body of the report contains an address box, letter body, followed by a table that contains all the people to be printed on the cards. If the number exceeds 6 people, another page is to be printed, with no address or letter body, but the table with the remainder of the people on it.
I have been able to get everything to work except for the location of the table on the subsequent pages. They do not appear at the bottom as they do on the first page, but at the top of the page. I've tried using a rectangle as a container for the table, with no luck.
I seem to remember doing this in the past, but as projects get shelved for an extended period of time, the technical knowledge tends to fade.
Any suggestions would be greatly appreciated. Thanks
We are facing some issues with SSIS and thought if you could be of some help.
Actually, instead of Deploying our SSIS projects we copy the SSIS files to a different server from where we execute the SSIS Packages. The reason being, we have a Core Version of the application and this is deployed to different markets and subsequent customisations are performed in the market specific version of the Packages. The problem is although we have a Package Configuration Xml File for our Connection object, the first time when we copy the Packages, we have to manually open each of the Package and change the URL of the Xml File for Configuration. I read through some of the articles on the internet and came to know that the Package Configuration does not support relative path and only absolute path is possible.
Can you please share some of your suggestions on the above problem.
Hi,
I have a table with a BLOB column, and I need to populate this table including the BLOB column (image type in the database).
What I have done is:
1. use a flat file transform to read a .csv file which specifies the names of the files that store the binary contents for the BLOB column for each row.
2. use an Import Column Transform to read the binary files.
3. use an OLE DB Dest transform to dump the data into my destination table.
I got the error saying:
Error: 0xC02090BB at XXXX, Import Column [1]: Opening the file ".diagram1.bin" for reading failed. The file was not found.
I guess this is because my file "diagram1.bin" is not in the current path? (The current path can be found by "System.IO.Directory.GetCurrentDirectory() call, in my case it is "c:program filesmicrosoft visual studio 0common7IDE".)
My question is: how to determine the directory path information of the package I am running?
Thanks!
Wenbiao
Help!!!
I want to install SQLExp2K5 with Advanced Services on a standalone basis on my Dell Inspiron XPS laptop (Win XP sp2, .NET 2.0; 2gb RAM; 8mb broadband; hardware firewall/router) so that I can learn SQL/VB/VWD, and want to use SQL E 2K5 without internet connectivity (at least until I know what the hell I'm doing!)
Really confused as to what component(s), and services I should/should not install - so I've listed my questions below:
1. Service Account: As I'm not on / dont have a domain, i presume using a domain user account is not an option for me. Should I use the Local Service account ? If so, what are/can be the issues in doing so?
2. Authentication Mode: Is Windows Authentication the one to use for a standalone install?; how would I enable the "sa" account and set an "sa" password? In fact, do I even need an"sa" account / password for a standalone, non internet connected install?
3. Components at install: Is it OK to install all components first off?
4. IIS: Is it possible to install IIS and not allow it access to the internet (or is this a stupid question -bearing in mind I dont really know what IIS is/does)
5: Business Intelligence Development Suite: How do I add BIDS once (IF) I manage to install SQLExp2K5-Adv.Ser,?
6. SQLExp2K5 Books on-line: Am I right in thinking that I should install this after installing SQLExp2K5?
7: Visual Basic Express/Visual Web Developer Express: Do these need to be configured seperately to work with SQLExp2K5?
Sorry about the length of this post, but have tried to be as clear as possible. Also apologise in advance for silly/stupid questions :)
All help and advice greatly appreciated.
Thanks,
sqlexpressbeginner
We have the customer requirement to display the footer of a SSRS Report fixed at the absolute bottom of a DIN A4 format page. The footer contains information like company address and stuff.
I searched quite a while on this topic and only found workarounds for SQL Server 2005 with Custom Code in the SSRS Report to calculate the size of the body content and then insert some empty lines to get the space needed to push the footer to the bottom of the page. But this won't work in SQL Server 2012. And I wasn't able to figure out how to achieve this yet.
I have, a SSAS 2012 tabular instance with SP2, there is a database on the instance with a read role with everyone assigned permissions. When configuring the Power BI analysis services connector, at the point where you enter Friendly Name, Description and Friendly error message, when you click next I receive the error "The remote server returned an error (403)." I've tested connecting to the database from Excel on a desktop and connect fine.I don't use a "onmicrosoft" account so don't have that problem to deal with.
We use Power BI Pro with our Office 365. As far as I can tell that part is working ok as I pass that stage of the configuration with a message saying connected to Power BI.The connector is installed on the same server as tabular services, its a Win2012 Standard server. The tabular instance is running a domain account that is the admin account for the instance (this is a dev environment) that account is what I've used in the connector configuration. It's also a local admin account. There is no gateway installed on the server.
I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS. In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task. The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube. This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.
I would expect that once development is done, and it is processed and deployed, that is it. My thinking is that the SSIS task should just update the already deployed cube,
How to right choose key column in"Mining Structure" for Microsoft Analysis Services?
I have table:
"Incoming goods"
Create table Income (
ID int not null identity(1, 1)
[Date] datetime not null,
GoodID int not null,
PriceDeliver decimal(18, 2) not null,
PriceSalse decimal(18, 2) not null,
CONSTRAINT PK_ Income PRIMARY KEY CLUSTERED (ID),
CONSTRAINT FK_IncomeGood foreign key (GoodID) references dbo.Goods ( ID )
)
I'm trying to build a relationship(regression) between “Price Sale” from Good and “Price Deliver”.But I do not know what column better choose as “key column”: ID or GoodID ?
It is possible to create Analysis Services project (*.dwproj) in Visual Studio 2012 Data Tools?
View 5 Replies View RelatedI am using
SSAS: version 2008
Excel: version 2013
I am connecting to SSAS cube from Excel and I have date dimension with 4 fields (I have others but I don't use it for this case). I created 4 fields in order to test all possible scenarios that I could think of:
DateKey:
- Type: System.Integer
- Value: yyyyMMdd
Date:
- Type: System.DateTime
DateStr0:
- Type: System.String
- Value: dd/MM/yyyy (note: I am not using US culture)
- Example: 01/11/2015
DateStr1:
- Type: System.String
- Value: %d/%M/yyyy (note: I am not using US culture)
- Example: 1/11/2015
Filtering on date is working fine:
Initially, in excel, filtering on date was not working. But after changing dimensional type to time, and setting DataType to Date, as mentioned in [URL] filter is working fine as you can see in the picture.Grouping on date is not working:
I have hierarchy in my Date dimension and I can group based on hierarchy, no problem. But user is used to pre-build grouping function of excel, and he wants to use that. Pre-build functions of Excel, Group and ungroup seems to be available as you can see in following picture:
But when user clicks 'Group', excel groups it as if it is a string, and that is the problem. User wants to group using pre-build grouping function available in Pivot table. I also find out that Power Pivot Table does not support this excel grouping functionality. And if I understood well, this pre-build grouping functionality of excel, needs to do calculation at run time, and that is not viable solution if you have millions of rows. So Power pivot table does not support pre-build grouping functionality of excel and hence we need to use dimension hierarchy to do the grouping. But I am not using Power Pivot table, I am using simple Pivot Table. So I expect grouping functionality to be working fine. Then I tried to do simple test. I created a simple data source in excel itself. And use it as source of my Pivot table. Then grouping is working fine. The only difference that I can see is (When double click the Measure value in Excel),For date values of my simple test, excel consider them as 'Date'.
For date values of my data coming from cube, excel consider them as 'General'
2.1. But value here is same as it was in simple test.
2.2. 'Date Filter' works just fine.
2.3. If I just select this cell and unselect it, then excel change type to 'Date' though for that cell.
2.4. I have created 4 different types of fields in my date dimension thinking that values of attribute of my dimension might be the problem, but excel consider 'General' for all of them.
2.5 This value (that can be seen when double clicking on measure) comes from 'Name Column' of the attribute. And the DataType defined is WChar. And I thought that might be the reason of issue. And I changed it to 'Date'. But SSAS does not allow it to change to 'Date' giving error : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.
So, I don't know, what is the puzzle piece that I am missing.
1. Date filter works, group does not work
2. Excel consider it as 'General' string.
3. SSAS does not allow to change 'NameColumn' to Date.
I would like to know the best practice for running analysis service in terms of port usage. Is it better to run on a specific port or have dynamic ports ? We have clustered servers that run default on 2383 but not sure with non clustered what's the best way to get performance.
View 2 Replies View RelatedI want to find out if it is possible to create a role in a cube without an AD account, e.g. using a GMail email address.
View 3 Replies View RelatedHi,
I have some questions about SQL Servers 2000 and 2005 compatibility.
In my configuration I have to use both servers.
The cubes are stocked in 2005 server.
May I transfer from 2005 to 2000 Analysis Services the cubes?
If yes, what is the procedure? The result of migration is the same in the two different versions?
If not, how can I solve this problem?
Thanks in advance.
I'm looking to see if I have a issue with excessive IO activity caused by less-than-ideal access patterns.
So I've run this query to look at logical and physical reads for logical writes:
selectsql_handle, plan_handle,
total_physical_reads, last_physical_reads,
min_physical_reads, max_physical_reads,
total_logical_writes, last_logical_writes,
min_logical_writes, max_logical_writes,
total_logical_reads, last_logical_reads,
min_logical_reads, max_logical_reads
fromsys.dm_exec_query_stats
The question is, when can it be considered excessive?
Or can you just order the results by descending order and see if you can make improvements on the worst ones?
The same goes for this query for pageiolatch wait counters and times for indexes:
select database_id, object_id, index_id, partition_number, page_io_latch_wait_count, page_io_latch_wait_in_ms
from sys.dm_db_index_operational_stats (null,null,null,null)
I have SQL query, and I am trying to write MDX with similar logic. Logic has where clause:
Where (((CD_TYP_CAT_PARENT = 'LOAD' OR CD_TYP_CAT_PARENT = 'SPEND') AND (CD_TYP_CAT NOT IN('REVERSAL','REFUND','FEE')))
OR (CD_TYP_CAT_PARENT = 'OTHER' AND CD_TYP_CAT = 'SPEND'))
how do I write this in MDX?
I just ran a testbed of 4 types of SQL queries:1. inline SQL with a StringBuilder2. managed sql3. SQL text processing (@SQL as varchar(5000); SET @SQL = 'SELECT ' + @var...)4. a regular sproc that has the columns and table name hard coded1,2, and 4 always end up at about the same time given the averages.3 is always at last 1.5 times slower, and usually closed to 2 times.1 and 2 both use StringBuilders, the code is a direct copy, and 3 is a copy as well.My managed SQL is: [Microsoft.SqlServer.Server.SqlProcedure] public static void usp_Items_Select_Managed(SqlString table, SqlString name, SqlString value) { // sql StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendFormat( "SELECT {0}.* FROM {0} WHERE {0}.{1} = {2}", table, col, value ); SqlConnection sqlConnection = new SqlConnection("context connection=true"); SqlCommand sqlCommand = new SqlCommand(stringBuilder.ToString(), sqlConnection); sqlConnection.Open(); SqlContext.Pipe.Send(sqlCommand.ExecuteReader()); sqlConnection.Close(); }Is there anything wrong with my Managed SQL, or is this just the way that it is?Thanks
View 1 Replies View RelatedHello ,
I have a SQL 2000 with Analysis server installed on it . Its in a different domain . When i am trying to register the server remotely i.e from another domain ,from another server it gives me an error that :
'Cannot connect to the repository .
Analysis Server : <Server_name>
Error : Cannot open database requested in Login '<Database_name > Login fails . Do you wish to register this server ? '
Both the domains are trusted . Except that the OS where the Analysis server is running is Windows 2000 . I am trying to connect through SQL 2000 Server with Windows NT 4.0 Server .
We have another Analysis server which is running but on Windows NT 4.0 OS where i can easily register that server .
I tried providing access with all the rights to concerned domains , but in vain .
I am exhausted trying to figure out the problem .
Any assistance in this regard would be of great help to me .
Thanks
Ravi .
Hello,
I am tuning some queries and am using the Display Execution Plan option from Query Analyzer. What I am looking to for is somewhere which will explain the differences between the costs displayed when you put the mouse pointer over the object in the Execution plan output. BOL gives some general directions, but I need more specifics.
Any help on this would be appreciated
Thanks,
Bill
Hi all,
Is there any method by which I can use Stored procedures in Analysis services. I have some procedures which uses Temprorary tables. I wanna use those procedures's columns. Is it possible?
Thanks in advance!
Rahul