I am having trouble connecting to a named instance on a different domain. For example, I am in the domain mydomain.com, the machine is in ops.mydomain.com.
In Query Analyzer I have tried to connect (without success) to:
machinename\instance
machinename
i.p. address
i.p. address\instance
Within the same domain I can successfully connect to
machinename\instance
Any ideas? Also, this machine is part of a cluster...not sure if that matters. I have full permissions to connect to this machine.
ThanksNever mind...the instance was setup to a port different than 1433. We changed it to 1433, rebooted the server, and now I can connect.
One issue though: I can only connect by specifying the i.p. address, i.e.
255.255.255.0\instancename
Using the server name or setting up an alias doesn't seem to work.
Originally posted by banderson
I am having trouble connecting to a named instance on a different domain. For example, I am in the domain mydomain.com, the machine is in ops.mydomain.com.
In Query Analyzer I have tried to connect (without success) to:
machinename\instance
machinename
i.p. address
i.p. address\instance
Within the same domain I can successfully connect to
machinename\instance
Any ideas? Also, this machine is part of a cluster...not sure if that matters. I have full permissions to connect to this machine.
Thanks
Showing posts with label trouble. Show all posts
Showing posts with label trouble. Show all posts
Thursday, 22 March 2012
Connect to MySQL in SQL Server 2005
I am having trouble connecting to a MySQL database in SQL Server 2005. How can this be done in the easiest fashion?
Dear MattD,
Are you trying to import data from MySql to Sql server 2005?
HTH,
Suprotim Agarwal
|||Moved to SQL Server Data Access.|||
With which functionality are you trying to connect to SQL Server ? DTS / SSIS / Linked Server / Adhoc remote access ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
Connect to MySQL in SQL Server 2005
I am having trouble connecting to a MySQL database in SQL Server 2005. How can this be done in the easiest fashion?
Dear MattD,
Are you trying to import data from MySql to Sql server 2005?
HTH,
Suprotim Agarwal
|||Moved to SQL Server Data Access.|||
With which functionality are you trying to connect to SQL Server ? DTS / SSIS / Linked Server / Adhoc remote access ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
Sunday, 11 March 2012
connect dbase II in ssis
I am having trouble connecting dbf of version dbase II. I can connect dbase III and dbase IV in ssis package,but can't connect dbase II.hope your help!who can help me|||
MS does not support DBBase II with their solution (JET). See the supported versions listed here-
How to use dBASE data with Access and Jet
(http://support.microsoft.com/kb/230125/en-us)
You will need to find a third-party driver, for ODBD, OLE-DB or ADO.Net. Saying that, it is rather old now, and I have never seen one. If you can understand the file format, then you could write your own source and/or connection manager.
|||
have you tried saving the dbf as a version III or IV file?liguancong wrote:
I am having trouble connecting dbf of version dbase II. I can connect dbase III and dbase IV in ssis package,but can't connect dbase II.hope your help!
Thursday, 8 March 2012
Confusion on Cube Design
Hello everyone. I'm having a bit of trouble getting my head around designing a new cube and I'm hoping this forum can help. I have a database which contains the following simplified structure:
tbl_Panelist:
panelist_id
tbl_Question:
question_id
tbl_Answer:
answer_id
tbl_Result_Set:
panelist_id
question_id
answer_id
I'm trying to design a cube which will allow analyzing of the counts of how many panelists answered each question by each answer. For example if Q1 has possible answers of A,B,C and Q2 has possible answers of X,Y, I'd like to be able to browse the cube and see
X Y
A 10 5
B 3, 2
indicating that 10 people answered Q1 with A and Q2 with X. My current thinking has been to create a view for each question and create a dimension off of that. Is this the correct method in a case like this? Any help would be greatly appreciated. Thank you.
Alex Levin
Principal Consultant
Fifth Marker Consulting, LLC
alex.levin@.fifthmarker.com
Hello Alex,
I was able to create an AS solution for your case simply by using the cube wizard. Basically you create 3 dimensions - Panelists, Questions, Answers bound to the respective tables and tell the cube wizard that your fact table is ResultSet, while including the existing dimensions. The resulting cube will provide additional analysis by Panelists, if needed.
If you write me at this address: andrewgaATnetzeroDOTcom i will send you a zip file with the AS solution, which you can deploy onto your server.
Andrew
|||Thank you for responding. That is exactly what I attempted initially. However, I could not have different Questions act as different dimensions through that technique. I should say that at this point, due to the views, I am not having problems creating the dimensions. My current problem lies with attempting to create measures that get "hit" by every dimension.
To make this even more complicated, a single person can answer some of the questions with multiple answers. Thus, I need to have two different measures, one for a raw count of the answers, and the other with a distinct count of each panelist's answers.
Finally, I should also mention that I am using SQL Server 2005 and SSAS 2005 to do this project. Thanks again for any help that could be provided.
Alex Levin
Principal Consultant
Fifth Marker Consulting, LLC
alex.levin@.fifthmarker.com
|||Hello Alex,
> distinct count of each panelist's answers.
Could you please clarify? When you browse this measure and panelists dimension, do you want to see how many *questions* each panelist answered with at least one answer? Also, do you need it as a measure, which should also behave nice when user browses not panelists dimension, or all you need is some MDX query, which would fetch this information (calc measure within one MDX query)?
I am asking because in my test i created a Count measure bound to the row of the fact table. When i browse the panelists dimension and that measure i do get the numbers of answers provided by each panelist. I suppose you gave it "raw answers" name.
Andrew
|||I'm not exactly sure of the correct terminology here, so perhaps a continuation of my first example will explain what I'm looking for. Using the same table structure mentioned previously, suppose three panelists, p1, p2, and p3. We'll also suppose two questions, q1 and q2. Finally, q1 has possible answers A-E and q2 has possible answers W-Z. Here are the responses of each:
P1 A, C, D, W, Y, Z
P2 A, B, W, Z
P3 A, G, X, Z
Using this data, I would like to be able to build a pivottable where I can have both Q1 and Q2 as seperate dimensions. Keep in mind, in my actual project, there are closer to 40 questions, rather than just 2. For now I'd just like to focus on one measure as there are other factors involved in how the questions are answered. I would expect the results of this pivottable to looks something like:
W X Y Z
A 2 1 1 3
B 1 0 0 1
C 1 0 1 1
D 1 0 1 1
E 0 0 0 0
The trouble I seem to be running into is that when I try and examine dimesions with a granularity based on the answerID, I am getting no "hits". When there is a one to one relationship between the answer and the panelist, I am able to have a dimension with a granularity of the panelistID. In this case I get the results exactly as expected. I can even examine a granularity of panelist against a granularity of answer and get the result I expect. It's the answer to answer where I am currently having problems. Thanks again for your help.
Alex Levin
Principal Consultant
Fifth Marker Consulting, LLC
alex.levin@.fifthmarker.com
|||Just to move this along a bit more, the crux of the problem that I am having is that for questions (dimensions) that allow multiple answers by the same person I cannot browse those two dimensions in the pivottable. I have now changed their granularity to be a collection of the answerID and the panelistID. While this seems to be a step closer, I still cannot get "hits" off of them. If this were a SQL query, I would just do an intersect on the results of selects of the panelist per question query. Once again, any help would be greatly appreciated. I'd be happy to further clarify what I've done if any of this is unclear. Thank you.
Alex Levin
Principal Consultant
Fifth Marker Consulting, LLC
alex.levin@.fifthmarker.com
|||Hello Alex,
I have just sent you my version of the solution but that one was based on the first post.
Your second post needs clarifications. In your matrix of the results, which you would like to obtain, let's discuss the left-top cell.
W
A 2
From your description, A and W are the instances of answers. Basically you have put the same dimension "Answers" on columns and rows axes. This can't be.
Anyway, how the value of 2 was computed? From your description the only correlation i can find is "the number of panelists, who gave both answers".
|||Hello Andrew. Thank you for taking the time to help me. I will attempt to clear this up a bit. A and W are exactly what I need to create a pivottable on as they represent the results of two different questions. I have attempted to overcome this through the use of views containing the results of each question. So, to continue with the example above, I have in my database and data source view:
View_Q1 - panelist_ID, question_ID, answer_ID where question_ID = Q1
View_Q2 - panelist_ID, question_ID, answer_ID where question_ID = Q2
Keep in mind I'm simplifying this as of course you'd want the friendly names and some other information in addition to the ID's for actually building the dimensions. I have then created a dimension from each view table and used my master results table as the Measures table.
This all works fine when comparing questions with a 1-1 (one to one) panelist to answer relationship and even a single 1-M (one to many) question compared to a 1-1 table. My problem comes from comparing two 1-M questions. I've set the granularity in these cases to be the collection of answer and panelist. Perhaps using views is completely the wrong approach, but it certainly seems like I'm pretty close. The example above is of two 1-M questions.
Finally, to answer your second question, 2 is the count of panelists who answered both A and W. In this example that would actually be panelists P1 and P2. I'm only concerned with the count of panelists, and not with actually identifiying the panelists in any way. Does that explain what I am trying to accomplish or is there still something else that I can clear up? Once again, thank you for your help.
Alex Levin
Principal Consultant
Fifth Marker Consulting, LLC
alex.levin@.fifthmarker.com
|||Hello Alex,
It might take some time till i will be able to think how to resolve your problem by means of OLAP, but having read your goal i wanted to ask some questions if you were really determined to solve it with OLAP.
The model of the cube should be good enough to resolve not just one report. Although it is also OK to build a cube for the sake of one report, it does not seem people do that.
In your case, what would be the value of your measure for the tuple (AnswersDim1.All, AnswersDim2.All)? Is it the number of panelists answered all questions?
What would be the values for the tuple (AnswersDim1.A, AnswersDim2.All) and (AnswersDim1.All, AnswersDim2.A)?
Suppose you make both dimensions non-aggregatable. You would not need to think about the 2 questions above, but would need to think about the default members for the 2 dimensions. Are you planning to run MDX queries like:
select [measures].yourmeasure on 0,
AnswersDim1.members on 1
from Cube
Notice, that this query would need to fetch tuples like (AnswersDim1.currentmember, AnswersDim2.defaultmember).
Being not advanced in OLAP modeling personally, if my goal was to produce just that matrix report you described, i would have been done in one day by writing a C++ or C# program, which would open forward-only row-set for a view joining your tables and building in-memory structures based on STL (C++) or Collections.Generics (C#), calculating your goal, writing the result as a SQL table and binding the report to that already calculated table.
Of course, i would think more if the number of panelists were huge. I suppose the number of questions and possible answers is not big, because making the report the way you described would make it unusable.
Andrew
|||Unfortuantely, I fear that I must use OLAP for this project. The business requirements are that analysists must be able to view the responses to any combination of questions in a pivottable in Excel. To give you an idea of exactly how much data we're talking about here, there are currently about 60 questions with about 2-15 answers each. Currently there are a few thousand panelists, but this is expected to eventually reach into the hundreds of thousands. The database structure was designed to allow for this expected increase in data and for the easy ability to change questions and answers as time goes on.
You are correct in noticing that my aggregations are fairly meaningless in this project. There are some questions which have groupings of answers, but for the most part, each question only has one level in its heirarchy. Perhaps I can turn off aggregations in these cases if that will help.
To answer your questions specifically, Q1Dim.All,Q2Dim.All (I assume that's what you meant) would be 15 which represents the total number of responses to Q1 + the total number of responses to Q2. Had these both been 1-1 questions, that total would represent the union of panelists who answered each of the questions. Q1Dim.A, Q2Dim.All would be7 and Q2Dim.A, Q1Dim.All would be 5.
As I said at the outset, this is my first project using Analysis Server so it would not be surprising if I am going about this the wrong way or if I have missed some fundamental underlying concept. I was not expecting to have to write any MDX queries in this phase. What role does the defaultmember property play here?
Finally, as best as I can tell the crux of my problem may rest on being able to create a measure which will find results within a dimension using the collection of answerid and panelistid but find results between dimensions just using the panelistid. Does that sound correct to you based on your understanding of what I am trying to accomplish? Again, thanks for your time and interest on my behalf.
Alex Levin
Principal Consultant
Fifth Marker Consulting, LLC
alex.levin@.fifthmarker.com
|||I think I've moved forward a bit on this problem. As I'm not an expert on MDX, I've written this in SQL code. Hopefully someone out there can help me write an MDX based measure which will be the equivalent:
with a1 as (select distpanelistid from dbo.tbl_Survey_Results where surveyanswerid = 40 intersect select distpanelistid from dbo.tbl_Survey_Results where surveyanswerid = 491)
select count(distpanelistid) from a1
In this case I'm specifically getting the count for answers 40 and 491. I need the MDX to be of whatever answers I am currently browsing in the pivottable. Any MDX experts out there know this one?
Alex Levin
Principal Consultant
Fifth Marker Consulting, LLC
alex.levin@.fifthmarker.com
|||Yet another person raised similar task (topic: Need help designing the Cube). I did it in MSAccess, while i see that you made it with SQL. I also provided some thoughts why i failed to do it with OLAP.|||Ok, after much effort and with some amazing help from Matt Burr with the Analysis Server group at Microsoft, I have a solution to this problem. I am going to quote from the wrap up email provided by Matt. If anyone has any questions or would like a copy of the sample cube and db which was created for this, I would be happy to provide them. Thanks to Andrew Garbuzov for his help on this issue as well. From Matt Burr:
Resolution
In the end, the solution involved creating views against your source “fact” table to represent individual query dimensions. Then, we created the different query dimensions from these views. We also created a Panelist dimension that served two purposes: (1) it contains the data that we actually will count and (2) it serves to help join together the various question dimensions so that navigating one dimension effectively navigates the other dimensions.
Next, we created measures based on each of the question dimensions; these simply counted the unique number of participants that provided any given answer to a question, but their primary value is that they serve as a sort of “hook” that we can use to relate the various question dimensions back to the Panelist dimension, and from there out to the other question dimensions.
We then used the Dimension Usage tab to ensure that the Panelist dimension related to the measures that we had created from the question dimensions, and thus related to the question dimensions (so that navigating or “filtering” a given question dimension subsequently navigated/filtered the Panelist dimension), and we also created many-to-many relationships from each of the question dimensions to each of the other question dimensions (their relationship with the Panelist dimension facilitated this), so that navigating/filtering any one of the question dimensions consequently navigated/filtered all of the other question dimensions, based on a shared set of panelists that provided certain answers to both of the questions.
Finally, we created a calculated member that counts the distinct number of participants that exist in the Panelist dimension, which will have been navigated/filtered by your choice of question dimensions. This calculated member tells you what you wished to know: the number of panelists that answered x for one question AND y for another.
Alex Levin
Principal Consultant
Fifth Marker Consulting, LLC
alex.levin@.fifthmarker.com
Wednesday, 7 March 2012
Confusion on Cube Design
Hello everyone. I'm having a bit of trouble getting my head around designing a new cube and I'm hoping this forum can help. I have a database which contains the following simplified structure:
tbl_Panelist:
panelist_id
tbl_Question:
question_id
tbl_Answer:
answer_id
tbl_Result_Set:
panelist_id
question_id
answer_id
I'm trying to design a cube which will allow analyzing of the counts of how many panelists answered each question by each answer. For example if Q1 has possible answers of A,B,C and Q2 has possible answers of X,Y, I'd like to be able to browse the cube and see
X Y
A 10 5
B 3, 2
indicating that 10 people answered Q1 with A and Q2 with X. My current thinking has been to create a view for each question and create a dimension off of that. Is this the correct method in a case like this? Any help would be greatly appreciated. Thank you.
Alex Levin
Principal Consultant
Fifth Marker Consulting, LLC
alex.levin@.fifthmarker.com
Hello Alex,
I was able to create an AS solution for your case simply by using the cube wizard. Basically you create 3 dimensions - Panelists, Questions, Answers bound to the respective tables and tell the cube wizard that your fact table is ResultSet, while including the existing dimensions. The resulting cube will provide additional analysis by Panelists, if needed.
If you write me at this address: andrewgaATnetzeroDOTcom i will send you a zip file with the AS solution, which you can deploy onto your server.
Andrew
|||Thank you for responding. That is exactly what I attempted initially. However, I could not have different Questions act as different dimensions through that technique. I should say that at this point, due to the views, I am not having problems creating the dimensions. My current problem lies with attempting to create measures that get "hit" by every dimension.
To make this even more complicated, a single person can answer some of the questions with multiple answers. Thus, I need to have two different measures, one for a raw count of the answers, and the other with a distinct count of each panelist's answers.
Finally, I should also mention that I am using SQL Server 2005 and SSAS 2005 to do this project. Thanks again for any help that could be provided.
Alex Levin
Principal Consultant
Fifth Marker Consulting, LLC
alex.levin@.fifthmarker.com
|||Hello Alex,
> distinct count of each panelist's answers.
Could you please clarify? When you browse this measure and panelists dimension, do you want to see how many *questions* each panelist answered with at least one answer? Also, do you need it as a measure, which should also behave nice when user browses not panelists dimension, or all you need is some MDX query, which would fetch this information (calc measure within one MDX query)?
I am asking because in my test i created a Count measure bound to the row of the fact table. When i browse the panelists dimension and that measure i do get the numbers of answers provided by each panelist. I suppose you gave it "raw answers" name.
Andrew
|||I'm not exactly sure of the correct terminology here, so perhaps a continuation of my first example will explain what I'm looking for. Using the same table structure mentioned previously, suppose three panelists, p1, p2, and p3. We'll also suppose two questions, q1 and q2. Finally, q1 has possible answers A-E and q2 has possible answers W-Z. Here are the responses of each:
P1 A, C, D, W, Y, Z
P2 A, B, W, Z
P3 A, G, X, Z
Using this data, I would like to be able to build a pivottable where I can have both Q1 and Q2 as seperate dimensions. Keep in mind, in my actual project, there are closer to 40 questions, rather than just 2. For now I'd just like to focus on one measure as there are other factors involved in how the questions are answered. I would expect the results of this pivottable to looks something like:
W X Y Z
A 2 1 1 3
B 1 0 0 1
C 1 0 1 1
D 1 0 1 1
E 0 0 0 0
The trouble I seem to be running into is that when I try and examine dimesions with a granularity based on the answerID, I am getting no "hits". When there is a one to one relationship between the answer and the panelist, I am able to have a dimension with a granularity of the panelistID. In this case I get the results exactly as expected. I can even examine a granularity of panelist against a granularity of answer and get the result I expect. It's the answer to answer where I am currently having problems. Thanks again for your help.
Alex Levin
Principal Consultant
Fifth Marker Consulting, LLC
alex.levin@.fifthmarker.com
|||Just to move this along a bit more, the crux of the problem that I am having is that for questions (dimensions) that allow multiple answers by the same person I cannot browse those two dimensions in the pivottable. I have now changed their granularity to be a collection of the answerID and the panelistID. While this seems to be a step closer, I still cannot get "hits" off of them. If this were a SQL query, I would just do an intersect on the results of selects of the panelist per question query. Once again, any help would be greatly appreciated. I'd be happy to further clarify what I've done if any of this is unclear. Thank you.
Alex Levin
Principal Consultant
Fifth Marker Consulting, LLC
alex.levin@.fifthmarker.com
|||Hello Alex,
I have just sent you my version of the solution but that one was based on the first post.
Your second post needs clarifications. In your matrix of the results, which you would like to obtain, let's discuss the left-top cell.
W
A 2
From your description, A and W are the instances of answers. Basically you have put the same dimension "Answers" on columns and rows axes. This can't be.
Anyway, how the value of 2 was computed? From your description the only correlation i can find is "the number of panelists, who gave both answers".
|||Hello Andrew. Thank you for taking the time to help me. I will attempt to clear this up a bit. A and W are exactly what I need to create a pivottable on as they represent the results of two different questions. I have attempted to overcome this through the use of views containing the results of each question. So, to continue with the example above, I have in my database and data source view:
View_Q1 - panelist_ID, question_ID, answer_ID where question_ID = Q1
View_Q2 - panelist_ID, question_ID, answer_ID where question_ID = Q2
Keep in mind I'm simplifying this as of course you'd want the friendly names and some other information in addition to the ID's for actually building the dimensions. I have then created a dimension from each view table and used my master results table as the Measures table.
This all works fine when comparing questions with a 1-1 (one to one) panelist to answer relationship and even a single 1-M (one to many) question compared to a 1-1 table. My problem comes from comparing two 1-M questions. I've set the granularity in these cases to be the collection of answer and panelist. Perhaps using views is completely the wrong approach, but it certainly seems like I'm pretty close. The example above is of two 1-M questions.
Finally, to answer your second question, 2 is the count of panelists who answered both A and W. In this example that would actually be panelists P1 and P2. I'm only concerned with the count of panelists, and not with actually identifiying the panelists in any way. Does that explain what I am trying to accomplish or is there still something else that I can clear up? Once again, thank you for your help.
Alex Levin
Principal Consultant
Fifth Marker Consulting, LLC
alex.levin@.fifthmarker.com
|||Hello Alex,
It might take some time till i will be able to think how to resolve your problem by means of OLAP, but having read your goal i wanted to ask some questions if you were really determined to solve it with OLAP.
The model of the cube should be good enough to resolve not just one report. Although it is also OK to build a cube for the sake of one report, it does not seem people do that.
In your case, what would be the value of your measure for the tuple (AnswersDim1.All, AnswersDim2.All)? Is it the number of panelists answered all questions?
What would be the values for the tuple (AnswersDim1.A, AnswersDim2.All) and (AnswersDim1.All, AnswersDim2.A)?
Suppose you make both dimensions non-aggregatable. You would not need to think about the 2 questions above, but would need to think about the default members for the 2 dimensions. Are you planning to run MDX queries like:
select [measures].yourmeasure on 0,
AnswersDim1.members on 1
from Cube
Notice, that this query would need to fetch tuples like (AnswersDim1.currentmember, AnswersDim2.defaultmember).
Being not advanced in OLAP modeling personally, if my goal was to produce just that matrix report you described, i would have been done in one day by writing a C++ or C# program, which would open forward-only row-set for a view joining your tables and building in-memory structures based on STL (C++) or Collections.Generics (C#), calculating your goal, writing the result as a SQL table and binding the report to that already calculated table.
Of course, i would think more if the number of panelists were huge. I suppose the number of questions and possible answers is not big, because making the report the way you described would make it unusable.
Andrew
|||Unfortuantely, I fear that I must use OLAP for this project. The business requirements are that analysists must be able to view the responses to any combination of questions in a pivottable in Excel. To give you an idea of exactly how much data we're talking about here, there are currently about 60 questions with about 2-15 answers each. Currently there are a few thousand panelists, but this is expected to eventually reach into the hundreds of thousands. The database structure was designed to allow for this expected increase in data and for the easy ability to change questions and answers as time goes on.
You are correct in noticing that my aggregations are fairly meaningless in this project. There are some questions which have groupings of answers, but for the most part, each question only has one level in its heirarchy. Perhaps I can turn off aggregations in these cases if that will help.
To answer your questions specifically, Q1Dim.All,Q2Dim.All (I assume that's what you meant) would be 15 which represents the total number of responses to Q1 + the total number of responses to Q2. Had these both been 1-1 questions, that total would represent the union of panelists who answered each of the questions. Q1Dim.A, Q2Dim.All would be7 and Q2Dim.A, Q1Dim.All would be 5.
As I said at the outset, this is my first project using Analysis Server so it would not be surprising if I am going about this the wrong way or if I have missed some fundamental underlying concept. I was not expecting to have to write any MDX queries in this phase. What role does the defaultmember property play here?
Finally, as best as I can tell the crux of my problem may rest on being able to create a measure which will find results within a dimension using the collection of answerid and panelistid but find results between dimensions just using the panelistid. Does that sound correct to you based on your understanding of what I am trying to accomplish? Again, thanks for your time and interest on my behalf.
Alex Levin
Principal Consultant
Fifth Marker Consulting, LLC
alex.levin@.fifthmarker.com
|||I think I've moved forward a bit on this problem. As I'm not an expert on MDX, I've written this in SQL code. Hopefully someone out there can help me write an MDX based measure which will be the equivalent:
with a1 as (select distpanelistid from dbo.tbl_Survey_Results where surveyanswerid = 40 intersect select distpanelistid from dbo.tbl_Survey_Results where surveyanswerid = 491)
select count(distpanelistid) from a1
In this case I'm specifically getting the count for answers 40 and 491. I need the MDX to be of whatever answers I am currently browsing in the pivottable. Any MDX experts out there know this one?
Alex Levin
Principal Consultant
Fifth Marker Consulting, LLC
alex.levin@.fifthmarker.com
|||Yet another person raised similar task (topic: Need help designing the Cube). I did it in MSAccess, while i see that you made it with SQL. I also provided some thoughts why i failed to do it with OLAP.|||Ok, after much effort and with some amazing help from Matt Burr with the Analysis Server group at Microsoft, I have a solution to this problem. I am going to quote from the wrap up email provided by Matt. If anyone has any questions or would like a copy of the sample cube and db which was created for this, I would be happy to provide them. Thanks to Andrew Garbuzov for his help on this issue as well. From Matt Burr:
Resolution
In the end, the solution involved creating views against your source “fact” table to represent individual query dimensions. Then, we created the different query dimensions from these views. We also created a Panelist dimension that served two purposes: (1) it contains the data that we actually will count and (2) it serves to help join together the various question dimensions so that navigating one dimension effectively navigates the other dimensions.
Next, we created measures based on each of the question dimensions; these simply counted the unique number of participants that provided any given answer to a question, but their primary value is that they serve as a sort of “hook” that we can use to relate the various question dimensions back to the Panelist dimension, and from there out to the other question dimensions.
We then used the Dimension Usage tab to ensure that the Panelist dimension related to the measures that we had created from the question dimensions, and thus related to the question dimensions (so that navigating or “filtering” a given question dimension subsequently navigated/filtered the Panelist dimension), and we also created many-to-many relationships from each of the question dimensions to each of the other question dimensions (their relationship with the Panelist dimension facilitated this), so that navigating/filtering any one of the question dimensions consequently navigated/filtered all of the other question dimensions, based on a shared set of panelists that provided certain answers to both of the questions.
Finally, we created a calculated member that counts the distinct number of participants that exist in the Panelist dimension, which will have been navigated/filtered by your choice of question dimensions. This calculated member tells you what you wished to know: the number of panelists that answered x for one question AND y for another.
Alex Levin
Principal Consultant
Fifth Marker Consulting, LLC
alex.levin@.fifthmarker.com
Sunday, 19 February 2012
Configuring Text Log File Path in dtxConfig
Hi,
I am having trouble configuring the log file path in dtsconfig. I don't want to keep changing the log file path every time I change environments.
Here are the parameters.
<Configuration ConfiguredType="Property" Path="\Package.LogProviders[SSIS log provider for Text files].Properties[ConfigString]" ValueType="String">
<ConfiguredValue>SalesForceConnectLog</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.LogProviders[SSIS log provider for Text files].Properties[Name]" ValueType="String">
<ConfiguredValue>SSIS log provider for Text files</ConfiguredValue>
</Configuration>
I tried to enter the path on [ConfigString] but it didn't work.
Any Help is appreciated
Gulden
You don't update the log provider, you update the connection manager for that log provider.|||You are absolutely right. Your email also reminded me why I had ruled that out. It was a problem with logging to the same file from two different packages. I went back and corrected that.
Thanks for your answer..
Tuesday, 14 February 2012
Configuring Roles/Membership SQL to not use DBO?
I have a site with roles/membership working fine on my local machine, but I am having serious trouble deploying it to a shared host. My hosting provides a sql 2000 database, but the user created for it that I use is not dbo. I am errors when I try to access my site such as
Couldnot find storedprocedure'dbo.aspnet_CheckSchemaVersion'
The procedure was created under my user, so it's there, but it's full name ia myusername.aspnet_CheckSchemaVersion
How can I tell the asp.net membership/role provider to use my username instead of dbo when it looks for objects?
Thanks
Configure your website to use a user with myusername as the default schema for your role/membership. By default the role/membership use a connection string named 'LocalSqlServer', so you can change login information of this connection string. For details, please refer to this article:
http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx
|||
I had my username configured in the connection string, it was just ignoring it. Well, it was connecting to the SQL server with my username, but was still looking for stored procedures using dbo.
Luckily, the blog you linked to did have the source code for the default MS SQL providers. When I looked at the source code for Microsoft's membership/roles providers I found out why it wasn't working. dbo is hard coded into all the stored procedure calls. Makes it kinda hard to use on shared servers where you aren't the dbo.
Anyway, I downloaded the source code, removed all the dbo. calls, recompiled and set up a custom provider to use my new dbo-free sql providers.
Works like a champ. So, consider this one solved.
asylumn:
I had my username configured in the connection string, it was just ignoring it. Well, it was connecting to the SQL server with my username, but was still looking for stored procedures using dbo.
This may because you have something like 'Integrated Security=SSPI' in the connection string, which force to connect using Windows Authentication so the username would be ignored. Still, I'm glad to hear you've solved this issue
You mentioned getting the source code for the MS SQL providers. Can you tell me where you got that? I found the Access provider athttp://msdn.microsoft.com/asp.net/beta2/providers/default.aspx from the link above. But I can't seem to find the SQL provider. Any help would be much appreciated. Thanks.
Hi jbutler76, Scott has repied you on his bolg, you may check here to find the SQL Provider Source:
http://weblogs.asp.net/scottgu/archive/2006/04/13/Source-Code-for-the-Built_2D00_in-ASP.NET-2.0-Providers-Now-Available-for-Download.aspx
Sunday, 12 February 2012
ConfigureUnDouble installation help
I am having trouble installing the ConfigureUnDouble tool. I followed all the directions, at least I though i did, and it keeps giving me this error message: "Falure initializing gacutil". I'm not sure if the gacutil.exe is failing or if the dll is not getting created properly. Can anyone help with this?
GACUTIL is a utility that comes as part of the .NET framework. If you open the Visual Studio 2005 Command Prompt and type GACUTIL /?, you should see a list of command line switches. If you don't, something is wrong with your .NET / Visual Studio install.Friday, 10 February 2012
Configure Server for Email Delivery
I am having trouble getting a report emailed for the first time.
The timed subscription has been set. I have updated the ReportServer.config
file to include the ip of the exchange server along with port number although
im not sure where to enter a password. The IT guys are making sure that the
exchange server will accept smtp requests from other servers. My question is
what is the best method to troubleshoot & is there anything gleeming that i
am missing. ThanksI have noted that the sql server agent has a job logged & shows suceeded when
last run but I have recieved any emails. I have also noted a job in the
process info that has a status of sleeping. Event log shows nothing
"Tango" wrote:
> I am having trouble getting a report emailed for the first time.
> The timed subscription has been set. I have updated the ReportServer.config
> file to include the ip of the exchange server along with port number although
> im not sure where to enter a password. The IT guys are making sure that the
> exchange server will accept smtp requests from other servers. My question is
> what is the best method to troubleshoot & is there anything gleeming that i
> am missing. Thanks|||Can anyone help, please. Maybe a link to a ReportServer.config that is
successfully emailing reports might help.
Thankyou
"Tango" wrote:
> I have noted that the sql server agent has a job logged & shows suceeded when
> last run but I have recieved any emails. I have also noted a job in the
> process info that has a status of sleeping. Event log shows nothing
> "Tango" wrote:
> > I am having trouble getting a report emailed for the first time.
> > The timed subscription has been set. I have updated the ReportServer.config
> > file to include the ip of the exchange server along with port number although
> > im not sure where to enter a password. The IT guys are making sure that the
> > exchange server will accept smtp requests from other servers. My question is
> > what is the best method to troubleshoot & is there anything gleeming that i
> > am missing. Thanks|||Tango,
Did you ever find out an answer to this? If so could you please share it?
"Tango" wrote:
> Can anyone help, please. Maybe a link to a ReportServer.config that is
> successfully emailing reports might help.
> Thankyou
> "Tango" wrote:
> > I have noted that the sql server agent has a job logged & shows suceeded when
> > last run but I have recieved any emails. I have also noted a job in the
> > process info that has a status of sleeping. Event log shows nothing
> >
> > "Tango" wrote:
> >
> > > I am having trouble getting a report emailed for the first time.
> > > The timed subscription has been set. I have updated the ReportServer.config
> > > file to include the ip of the exchange server along with port number although
> > > im not sure where to enter a password. The IT guys are making sure that the
> > > exchange server will accept smtp requests from other servers. My question is
> > > what is the best method to troubleshoot & is there anything gleeming that i
> > > am missing. Thanks|||It is notoriously difficult to get the subscriptions to work, I spent a week
configuring a production server trying to do this and gave up, I have the
feeling you need to re-install RS using different accounts/settings, which in
my case wasn't an option. I ended up coding a small windows service that
downloaded the report through a crafted URL and emailed it based on a
schedule in a table. It actually took less to code that snippet than to
configure the much glorified data driven subscription feature of RS.
Microsoft shouldn't be in such a hurry to advertise features that won't even
work in plain vanilla, no frills install out of the box.
Regards,
Tzepesh
"Tango" wrote:
> Can anyone help, please. Maybe a link to a ReportServer.config that is
> successfully emailing reports might help.
> Thankyou
> "Tango" wrote:
> > I have noted that the sql server agent has a job logged & shows suceeded when
> > last run but I have recieved any emails. I have also noted a job in the
> > process info that has a status of sleeping. Event log shows nothing
> >
> > "Tango" wrote:
> >
> > > I am having trouble getting a report emailed for the first time.
> > > The timed subscription has been set. I have updated the ReportServer.config
> > > file to include the ip of the exchange server along with port number although
> > > im not sure where to enter a password. The IT guys are making sure that the
> > > exchange server will accept smtp requests from other servers. My question is
> > > what is the best method to troubleshoot & is there anything gleeming that i
> > > am missing. Thanks|||Report server will only work against an SMTP server that accepts anonymous
or NTLM authentication. The best solution for email is to use a local SMTP
server as a relay to your smtp server. Set the pickup directory element in
the RSReportServer.config file to the local SMTP servers pickup directory
and the SendUsing element to 1. Then configure your local SMTP server to
relay the email to your actual SMTP server.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tzepesh" <Tzepesh@.discussions.microsoft.com> wrote in message
news:547E777A-F7A3-4F9A-A7AA-236F22E04966@.microsoft.com...
> It is notoriously difficult to get the subscriptions to work, I spent a
> week
> configuring a production server trying to do this and gave up, I have the
> feeling you need to re-install RS using different accounts/settings, which
> in
> my case wasn't an option. I ended up coding a small windows service that
> downloaded the report through a crafted URL and emailed it based on a
> schedule in a table. It actually took less to code that snippet than to
> configure the much glorified data driven subscription feature of RS.
> Microsoft shouldn't be in such a hurry to advertise features that won't
> even
> work in plain vanilla, no frills install out of the box.
> Regards,
> Tzepesh
> "Tango" wrote:
>> Can anyone help, please. Maybe a link to a ReportServer.config that is
>> successfully emailing reports might help.
>> Thankyou
>> "Tango" wrote:
>> > I have noted that the sql server agent has a job logged & shows
>> > suceeded when
>> > last run but I have recieved any emails. I have also noted a job in the
>> > process info that has a status of sleeping. Event log shows nothing
>> >
>> > "Tango" wrote:
>> >
>> > > I am having trouble getting a report emailed for the first time.
>> > > The timed subscription has been set. I have updated the
>> > > ReportServer.config
>> > > file to include the ip of the exchange server along with port number
>> > > although
>> > > im not sure where to enter a password. The IT guys are making sure
>> > > that the
>> > > exchange server will accept smtp requests from other servers. My
>> > > question is
>> > > what is the best method to troubleshoot & is there anything gleeming
>> > > that i
>> > > am missing. Thanks
The timed subscription has been set. I have updated the ReportServer.config
file to include the ip of the exchange server along with port number although
im not sure where to enter a password. The IT guys are making sure that the
exchange server will accept smtp requests from other servers. My question is
what is the best method to troubleshoot & is there anything gleeming that i
am missing. ThanksI have noted that the sql server agent has a job logged & shows suceeded when
last run but I have recieved any emails. I have also noted a job in the
process info that has a status of sleeping. Event log shows nothing
"Tango" wrote:
> I am having trouble getting a report emailed for the first time.
> The timed subscription has been set. I have updated the ReportServer.config
> file to include the ip of the exchange server along with port number although
> im not sure where to enter a password. The IT guys are making sure that the
> exchange server will accept smtp requests from other servers. My question is
> what is the best method to troubleshoot & is there anything gleeming that i
> am missing. Thanks|||Can anyone help, please. Maybe a link to a ReportServer.config that is
successfully emailing reports might help.
Thankyou
"Tango" wrote:
> I have noted that the sql server agent has a job logged & shows suceeded when
> last run but I have recieved any emails. I have also noted a job in the
> process info that has a status of sleeping. Event log shows nothing
> "Tango" wrote:
> > I am having trouble getting a report emailed for the first time.
> > The timed subscription has been set. I have updated the ReportServer.config
> > file to include the ip of the exchange server along with port number although
> > im not sure where to enter a password. The IT guys are making sure that the
> > exchange server will accept smtp requests from other servers. My question is
> > what is the best method to troubleshoot & is there anything gleeming that i
> > am missing. Thanks|||Tango,
Did you ever find out an answer to this? If so could you please share it?
"Tango" wrote:
> Can anyone help, please. Maybe a link to a ReportServer.config that is
> successfully emailing reports might help.
> Thankyou
> "Tango" wrote:
> > I have noted that the sql server agent has a job logged & shows suceeded when
> > last run but I have recieved any emails. I have also noted a job in the
> > process info that has a status of sleeping. Event log shows nothing
> >
> > "Tango" wrote:
> >
> > > I am having trouble getting a report emailed for the first time.
> > > The timed subscription has been set. I have updated the ReportServer.config
> > > file to include the ip of the exchange server along with port number although
> > > im not sure where to enter a password. The IT guys are making sure that the
> > > exchange server will accept smtp requests from other servers. My question is
> > > what is the best method to troubleshoot & is there anything gleeming that i
> > > am missing. Thanks|||It is notoriously difficult to get the subscriptions to work, I spent a week
configuring a production server trying to do this and gave up, I have the
feeling you need to re-install RS using different accounts/settings, which in
my case wasn't an option. I ended up coding a small windows service that
downloaded the report through a crafted URL and emailed it based on a
schedule in a table. It actually took less to code that snippet than to
configure the much glorified data driven subscription feature of RS.
Microsoft shouldn't be in such a hurry to advertise features that won't even
work in plain vanilla, no frills install out of the box.
Regards,
Tzepesh
"Tango" wrote:
> Can anyone help, please. Maybe a link to a ReportServer.config that is
> successfully emailing reports might help.
> Thankyou
> "Tango" wrote:
> > I have noted that the sql server agent has a job logged & shows suceeded when
> > last run but I have recieved any emails. I have also noted a job in the
> > process info that has a status of sleeping. Event log shows nothing
> >
> > "Tango" wrote:
> >
> > > I am having trouble getting a report emailed for the first time.
> > > The timed subscription has been set. I have updated the ReportServer.config
> > > file to include the ip of the exchange server along with port number although
> > > im not sure where to enter a password. The IT guys are making sure that the
> > > exchange server will accept smtp requests from other servers. My question is
> > > what is the best method to troubleshoot & is there anything gleeming that i
> > > am missing. Thanks|||Report server will only work against an SMTP server that accepts anonymous
or NTLM authentication. The best solution for email is to use a local SMTP
server as a relay to your smtp server. Set the pickup directory element in
the RSReportServer.config file to the local SMTP servers pickup directory
and the SendUsing element to 1. Then configure your local SMTP server to
relay the email to your actual SMTP server.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tzepesh" <Tzepesh@.discussions.microsoft.com> wrote in message
news:547E777A-F7A3-4F9A-A7AA-236F22E04966@.microsoft.com...
> It is notoriously difficult to get the subscriptions to work, I spent a
> week
> configuring a production server trying to do this and gave up, I have the
> feeling you need to re-install RS using different accounts/settings, which
> in
> my case wasn't an option. I ended up coding a small windows service that
> downloaded the report through a crafted URL and emailed it based on a
> schedule in a table. It actually took less to code that snippet than to
> configure the much glorified data driven subscription feature of RS.
> Microsoft shouldn't be in such a hurry to advertise features that won't
> even
> work in plain vanilla, no frills install out of the box.
> Regards,
> Tzepesh
> "Tango" wrote:
>> Can anyone help, please. Maybe a link to a ReportServer.config that is
>> successfully emailing reports might help.
>> Thankyou
>> "Tango" wrote:
>> > I have noted that the sql server agent has a job logged & shows
>> > suceeded when
>> > last run but I have recieved any emails. I have also noted a job in the
>> > process info that has a status of sleeping. Event log shows nothing
>> >
>> > "Tango" wrote:
>> >
>> > > I am having trouble getting a report emailed for the first time.
>> > > The timed subscription has been set. I have updated the
>> > > ReportServer.config
>> > > file to include the ip of the exchange server along with port number
>> > > although
>> > > im not sure where to enter a password. The IT guys are making sure
>> > > that the
>> > > exchange server will accept smtp requests from other servers. My
>> > > question is
>> > > what is the best method to troubleshoot & is there anything gleeming
>> > > that i
>> > > am missing. Thanks
Configure Report Server with SQL Server Express with Advanced Services.
I'm having a world of trouble with the Reporting Services Server. The install went fine. I went in to configure the Report Server and when creating the Report Server and the Report Manager virtual directories I get the following error: "The virtual directory could not be created. The previously set virtual directory will still be used." The problem with this is that I have not previously set a virtual directory. When clicking on the link I get the additional error information: "System.IO.FileNotFoundException:
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
at System.Management.ManagementObject.InvokeMethod(String methodName, ManagementBaseObject inParameters, InvokeMethodOptions options)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.CreateVirtualDirectory(String virtualDirectory, String path)"
When I check IIS the directories were created but the Reporting Services Configuration Manager does not see them unless I attempt to recreate them again without deleting them from IIS.
Anyone have any ideas as to this disconnect?
Thanks in advance!
By default RS is configured during installation so you shouldn't need to do any additional configuration. I'll ping the RS team to see if they have additional information about the error you're seeing.
Regards,
Mike Wachal
SQL Express team
-
Mark the best posts as Answers!
This one dropped off my radar, sorry.
Were you ever able to get your Report Server running correctly. It seems that I found a similar behavior that I could only create new RS virtual directories if the folder already existed.
Regards,
Mike
|||I have having precisely the same problem. I cannot install Report Services as part of the original install of SQL Server since it has an error. So I install report services separately, but if you do that, it will not auto-configure, and when I use the config tool, I get the same error above.|||It seemed to be related to an improper installation of ASP.NET. I uninstalled ASP 2.0, then uninstalled 1.1, and reinstalled both of these and then manual configuration worked.|||It is possible that you installed ASP.NET 2.0 before you installed IIS. The .NET Framework 2.0 has some special configurations that it does when IIS is installed that don't happen after the fact without running a special configuration file.
The alternative approach that you've found is to remove and reinstall the framework.
Regards,
Mike Wachal
SQL Express team
-
Mark the best posts as Answers!
No, I am 100% certain that IIS and the 1.1 framework were present before I installed 2.0. I even tried aspnet_regiis -i for both 1.1 and 2.0 and that did not help. Since I did not personally install the 1.1 framework, since both it and IIS were installed from someone elses disk image, I cannot guarantee that they were configured properly.
|||I am having problems with Reporting Services.
I go to http://localhost/reports and get error message:
"The report server is not responding. Verify that the report server is running and can be accessed from this computer."
In terms of configuration, I have set name of Report Server Virtual Directory to "ReportServer2", as it would not let me name it "ReportServer".
I have tried uninstalling .net framework v2 and reinstalling, but this does not help.
Any suggestions?
|||I'm trying to manually configure my second reporting server instance. I used reports/$rserver as the virtual directory, (rserver is my instance name), and got error message:
"The virtual directory could not be created. The previously set virtual directory will still be used."
Then i used only reports as the virtual dir, the configuration wizard liked it and let me go through. But when i trid to open the URL http://localhost/reports, got the error message:
"The report server is not responding. Verify that the report server is running and can be accessed from this computer."
I verified my instance "rserver" was running and i'm sure i installed IIS before I installed SDK.Looks like some of us got the same problems here, have you figured it out yet?
By the way, i installed my only SQL server instance SQLEXPRESS and my first reporting service instance by default. They work fine.
Configure Report Server with SQL Server Express with Advanced Services.
I am having trouble with SQL Express Reporting services.
When going to http:/localhost/reports I get the following error message:
"The report server is not responding. Verify that the report server is running and can be accessed from this computer."
In configuring the Report Server Virtual Directory, it would not let me set the name as "Report Server", so I named it "Report Server2".
I have tried uninstalling .NET framework v2 and reinstalling, but this does not help.
Any ideas?
By default, Express Advanced installs Report Server to a name instance, so you should be trying to get to http://localhost/reports$sqlexress. A default installation should have configured the virtual directory for you.
You can open SQL Server Configuration Manager and it will show you if the reporting service is running and if it has a name.
Mike
Configure Report Server with SQL Server Express with Advanced Services.
I'm having a world of trouble with the Reporting Services Server. The install went fine. I went in to configure the Report Server and when creating the Report Server and the Report Manager virtual directories I get the following error: "The virtual directory could not be created. The previously set virtual directory will still be used." The problem with this is that I have not previously set a virtual directory. When clicking on the link I get the additional error information: "System.IO.FileNotFoundException:
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
at System.Management.ManagementObject.InvokeMethod(String methodName, ManagementBaseObject inParameters, InvokeMethodOptions options)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.CreateVirtualDirectory(String virtualDirectory, String path)"
When I check IIS the directories were created but the Reporting Services Configuration Manager does not see them unless I attempt to recreate them again without deleting them from IIS.
Anyone have any ideas as to this disconnect?
Thanks in advance!
By default RS is configured during installation so you shouldn't need to do any additional configuration. I'll ping the RS team to see if they have additional information about the error you're seeing.
Regards,
Mike Wachal
SQL Express team
-
Mark the best posts as Answers!
This one dropped off my radar, sorry.
Were you ever able to get your Report Server running correctly. It seems that I found a similar behavior that I could only create new RS virtual directories if the folder already existed.
Regards,
Mike
|||I have having precisely the same problem. I cannot install Report Services as part of the original install of SQL Server since it has an error. So I install report services separately, but if you do that, it will not auto-configure, and when I use the config tool, I get the same error above.|||It seemed to be related to an improper installation of ASP.NET. I uninstalled ASP 2.0, then uninstalled 1.1, and reinstalled both of these and then manual configuration worked.|||It is possible that you installed ASP.NET 2.0 before you installed IIS. The .NET Framework 2.0 has some special configurations that it does when IIS is installed that don't happen after the fact without running a special configuration file.
The alternative approach that you've found is to remove and reinstall the framework.
Regards,
Mike Wachal
SQL Express team
-
Mark the best posts as Answers!
No, I am 100% certain that IIS and the 1.1 framework were present before I installed 2.0. I even tried aspnet_regiis -i for both 1.1 and 2.0 and that did not help. Since I did not personally install the 1.1 framework, since both it and IIS were installed from someone elses disk image, I cannot guarantee that they were configured properly.
|||I am having problems with Reporting Services.
I go to http://localhost/reports and get error message:
"The report server is not responding. Verify that the report server is running and can be accessed from this computer."
In terms of configuration, I have set name of Report Server Virtual Directory to "ReportServer2", as it would not let me name it "ReportServer".
I have tried uninstalling .net framework v2 and reinstalling, but this does not help.
Any suggestions?
|||I'm trying to manually configure my second reporting server instance. I used reports/$rserver as the virtual directory, (rserver is my instance name), and got error message:
"The virtual directory could not be created. The previously set virtual directory will still be used."
Then i used only reports as the virtual dir, the configuration wizard liked it and let me go through. But when i trid to open the URL http://localhost/reports, got the error message:
"The report server is not responding. Verify that the report server is running and can be accessed from this computer."
I verified my instance "rserver" was running and i'm sure i installed IIS before I installed SDK.Looks like some of us got the same problems here, have you figured it out yet?
By the way, i installed my only SQL server instance SQLEXPRESS and my first reporting service instance by default. They work fine.
Subscribe to:
Posts (Atom)